none
Excel 2013 Web Queries for Fidelity Website Secure Login RRS feed

  • Question

  • I attempted a small VBA Sub() in Excel 2013 with the intent of:
       - iterating through a list of high volume ETFs on the Main worksheet,
       - creating a Web Query for each ETF to access Security Holdings info for each ETF,
       - pull that data into an individual worksheet for each ETF.

    My issue is getting past the Fidelity secure access. The code below processes my password input correctly, but not my username. Being logged into the Fidelity website in an open browser session does not allow the web query access to the ETF data pages.

    Can anyone tell me why the username portion of the code is failing? Is there an easier way for me to skin this cat?

    Thanks

    =====

    Sub Update()
     ' Requires a reference to MICROSOFT INTERNET CONTROLS object
     On Error GoTo ErrorOut:

    Dim wbThis As Workbook: Set wbThis = ThisWorkbook
     Dim wsMain As Worksheet: Set wsMain = wbThis.Worksheets("Main")
     Dim objIntExp As InternetExplorer: Set objIntExp = New InternetExplorer
     Dim objIntExpDoc As Object
     Dim objIntExpDocElmt As Object
     Dim wsData As Worksheet
     Dim strUsername As Long
     Dim strPassword As String
     Dim lngDateLast As Long
     Dim lngDateCurrent As Long
     Dim strSymbol As String
     Dim strQuery As String
     Dim lngRow As Long
     Dim lngLength As Long
     Dim lngLastSymbolRow As Long
     Const str_LOGIN_PAGE As String = "https://oltx.fidelity.com/ftgw/fbc/ofsummary/defaultPage"
     Const str_START As String = "http://research2.fidelity.com/fidelity/screeners/etf/etfholdings.asp?symbol="
     Const str_END As String = "&view=Sector"
     Const lng_SYMBOL_LENGTH_MIN As Long = 3
     Const lng_SYMBOL_LENGTH_MAX As Long = 5
     Const lng_SYMBOL_ROW_MIN As Long = 2
     Const lng_SYMBOL_ROW_MAX As Long = 21

    ' Check for a new date
     lngDateLast = CLng(wsMain.Cells(5, 3))
     lngDateCurrent = CLng(Date)
     If lngDateLast >= lngDateCurrent Then
       MsgBox "TRY AGAIN", vbOKOnly, "Data is already current!"
       Exit Sub
     End If
     ' Delete any existing data sheets
     If wbThis.Worksheets.Count > 1 Then
       Application.DisplayAlerts = False
         For Each wsData In wbThis.Worksheets
           If wsData.Name <> "Main" Then
             wsData.Delete
           End If
         Next wsData
       Application.DisplayAlerts = True
     End If
     ' Get the last symbol row
     lngLastSymbolRow = wsMain.Range("A100").End(xlUp).Row
     If lngLastSymbolRow > lng_SYMBOL_ROW_MAX Then
       lngLastSymbolRow = lng_SYMBOL_ROW_MAX
     End If
     If lngLastSymbolRow < lng_SYMBOL_ROW_MIN Then
       MsgBox "TRY AGAIN", vbOKOnly, "No ETF Symbols detected in Column-A to process!"
       Exit Sub
     End If
     ' Set the Username and Password
     strUsername = Trim(CStr(InputBox("Enter your Fidelity Username for login.", "FIDELITY USERNAME")))
     strPassword = Trim(CStr(InputBox("Enter your Fidelity Password for login.", "FIDELITY PASSWORD")))
     ' Next line not required, but helps for debugging
     objIntExp.Visible = True
     ' Go to Fidelity LogIn page
     objIntExp.Navigate str_LOGIN_PAGE
     Do While objIntExp.Busy: DoEvents: Loop
     Do Until objIntExp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop
     ' Login with user inputs
     Set objIntExpDoc = objIntExp.Document
       Debug.Print "Form = " & objIntExpDoc.forms(0).Name
         Set objIntExpDocElmt = objIntExpDoc.getElementByID("userId-select")
           objIntExpDocElmt.Value = strUsername
             Debug.Print objIntExpDocElmt.Name & " = " & objIntExpDocElmt.Value
         Set objIntExpDocElmt = objIntExpDoc.getElementByID("password")
           ' Note - password disappears here!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
           objIntExpDocElmt.Value = strPassword
             Debug.Print objIntExpDocElmt.Name & " = " & objIntExpDocElmt.Value
       objIntExpDoc.forms(0).submit

    Do While objIntExp.Busy: DoEvents: Loop
     Do Until objIntExp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop
     ' Cycle through all symbols
     For lngRow = lng_SYMBOL_ROW_MIN To lngLastSymbolRow Step 1
       strSymbol = Trim(CStr(wsMain.Cells(lngRow, 1)))
       lngLength = Len(strSymbol)
       If (lngLength < lng_SYMBOL_LENGTH_MIN) Or (lngLength > lng_SYMBOL_LENGTH_MAX) Then
         ' Invalid symbol ... skip it
         GoTo GetNextSymbol:
       End If
       ' Build the web query URL
       strQuery = str_START & strSymbol & str_END
       ' Add a new worksheet
       wbThis.Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = strSymbol
       ' Get the raw ETF query data
       With wsMain.QueryTables.Add(Connection:=strQuery, Destination:=Worksheets(strSymbol).Range("A1"))
       .Name = strSymbol & "_Query"
       .RowNumbers = False
       .FillAdjacentFormulas = False
       .PreserveFormatting = True
       .RefreshOnFileOpen = False
       .BackgroundQuery = False
       .RefreshStyle = xlOverwriteCells
       .SavePassword = False
       .SaveData = True
       .AdjustColumnWidth = True
       .RefreshPeriod = 0
       .WebSelectionType = xlEntirePage
       .WebFormatting = xlWebFormattingNone
       .WebPreFormattedTextToColumns = True
       .WebConsecutiveDelimitersAsOne = True
       .WebSingleBlockTextImport = False
       .WebDisableDateRecognition = False
       .WebDisableRedirections = False
       .Refresh BackgroundQuery:=False
     End With

    GetNextSymbol:
     Next lngRow

    ' Refresh the date
     wsMain.Cells(5, 3) = lngDateCurrent

    NormalExit:
       Application.DisplayAlerts = True
       GoTo Cleanup:

    ErrorOut:
       MsgBox "Unable to update data!", vbOKOnly, "UPDATE ERROR"
      
     Cleanup:
       Application.DisplayAlerts = True
       Set objIntExpDocElmt = Nothing
       Set objIntExpDoc = Nothing
       Set objIntExp = Nothing
       Set wsData = Nothing
       Set wsMain = Nothing
       Set wbThis = Nothing

    End Sub

    Thursday, June 4, 2015 5:02 PM

Answers

  • Hi Bandit292,

    >> Can anyone tell me why the username portion of the code is failing? Is there an easier way for me to skin this cat?

    Do you mean that your username did not show correctly and your password shows correctly? If so, what do you mean by “Note - password disappears here”?

    I made a test with the code below, and it worked for me. If your username did not show up, I assume there are two parts, first is that you did set an empty value, for this situation, I suggest you put a breakpoint in the line which you set the username to see whether it set the username correctly. Second, you did not set the value to the right element in the html, I suggest you check the elementid of the Username.

    Sub login()
    ' open IE, navigate to the desired page and loop until fully loaded
        Set ie = CreateObject("InternetExplorer.Application")
        my_url = "https://hb2.bankleumi.co.il/e/Login.html"
        With ie
            .Visible = True
            .Navigate my_url
            .Top = 50
            .Left = 530
            .Height = 400
            .Width = 400
        Do Until Not ie.Busy And ie.readyState = 4
            DoEvents
        Loop
        End With
    ' Input the userid and password
        ie.document.getElementById("uid").Value = "testID"
        ie.document.getElementById("password").Value = "testPW"
    ' Click the "Search" button
        ie.document.getElementById("enter").Click
        Do Until Not ie.Busy And ie.readyState = 4
            DoEvents
        Loop
    End Sub

    >> Is there an easier way for me to skin this cat?

    I am afraid not, if you want to get the data of the website, you need to login it first, it depends on the website.

    Hope it will help.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Sunday, June 7, 2015 4:54 AM

All replies

  • Hi Bandit292,

    >> Can anyone tell me why the username portion of the code is failing? Is there an easier way for me to skin this cat?

    Do you mean that your username did not show correctly and your password shows correctly? If so, what do you mean by “Note - password disappears here”?

    I made a test with the code below, and it worked for me. If your username did not show up, I assume there are two parts, first is that you did set an empty value, for this situation, I suggest you put a breakpoint in the line which you set the username to see whether it set the username correctly. Second, you did not set the value to the right element in the html, I suggest you check the elementid of the Username.

    Sub login()
    ' open IE, navigate to the desired page and loop until fully loaded
        Set ie = CreateObject("InternetExplorer.Application")
        my_url = "https://hb2.bankleumi.co.il/e/Login.html"
        With ie
            .Visible = True
            .Navigate my_url
            .Top = 50
            .Left = 530
            .Height = 400
            .Width = 400
        Do Until Not ie.Busy And ie.readyState = 4
            DoEvents
        Loop
        End With
    ' Input the userid and password
        ie.document.getElementById("uid").Value = "testID"
        ie.document.getElementById("password").Value = "testPW"
    ' Click the "Search" button
        ie.document.getElementById("enter").Click
        Do Until Not ie.Busy And ie.readyState = 4
            DoEvents
        Loop
    End Sub

    >> Is there an easier way for me to skin this cat?

    I am afraid not, if you want to get the data of the website, you need to login it first, it depends on the website.

    Hope it will help.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Sunday, June 7, 2015 4:54 AM
  • Edward,

    Thanks for the good answer. I had my description correct, but the inline code comment was in error. The username input control value (when it was accessed in the debug window) was blank, but the password value was valid. It was, in fact, a case of using the wrong ID. Here is the HTML source for the username input control, and I had 3 different IDs to choose from:

    <div class="col-xs-11 col-md-8" id="fs-username-div">
      <select name="SSN" tabindex="1" disabled="disabled" class="fs-hidden form-control" id="userId-select" aria-hidden="true" aria-required="true" required="required" data-reset-text="Use a saved username">
        <option value="new">Add/Use another username</option>
      </select>
      <input tabindex="1" class="fs-mask fs-nounmask form-control" id="userId-input" aria-hidden="false" aria-required="true" aria-describedby="fs-user-info" required="required" type="text" maxlength="15" value="" autocomplete="off" data-msg-invalid="This field is not properly formatted" data-msg-required="Please enter your username" data-clear="true" data-masklength="-3" data-unmasked="" value="">
      </input>
      <input name="SSN" id="hiddenId" type="hidden" maxlength="15" value="">
      </input>
    </div>

    My login now works OK, but the code generates an error when I attempt to add the new web query (this line):

    With wsMain.QueryTables.Add(Connection:="URL;" & strQuery, Destination:=wbThis.Worksheets(strSymbol).Range("A1"))

       ... or ...

    With wsMain.QueryTables.Add(Connection:="URL;" & strQuery, Destination:=Range(strSymbol & "!$A$1"))

    I have yet to track down why.

    A different question ... how do you log out of the website when done, or do you just close the document?

    Monday, June 8, 2015 2:58 AM
  • Hi Bandit292,

    I am glad your original issue has been resolved.

    >> the code generates an error when I attempt to add the new web query (this line):

    For this new issue, I suggest you post a new thread for this, and it would be much helpful if you could share us your error message. There would be more community members to help you, and we could focus on the specific issue.

    >> how do you log out of the website when done, or do you just close the document?

    In my option, it depends on your own web page. Whether there is one button to log out? If it is, do you need to log out from the button. If you want to know more details about this issue, I suggest you post a new thread for this, and share us more information about your situation.

    Best Regardd,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, June 8, 2015 7:36 AM
  • Edward,

    Thanks, again, for the quick reply. A new thread may not be necessary.

    Upon further review of the QueryTable object model, I discovered I had overlooked the fact that the required Range parameter "must be on the worksheet that contains the QueryTable object". I was attempting to add the QueryTable object to the MAIN worksheet and pull data into separate individual DATA worksheets. I will revise the code to add each QueryTable object to the appropriate DATA worksheet and see what results I get.

    As for the LOG OUT option. The standard header banner on most Fidelity Web site pages contains a LOG OUT link. I will do some further investigation on this LOG OUT option to see if it is worth pursuit. For now, I will just close the Document when I have run all my web queries.

    Thanks again.

    Monday, June 8, 2015 5:32 PM
  • I spoke too soon about solving the secure login problem.

    When my solution of setting the Masked control value failed, I started poking around the secure Login page source code as I manually entered Username and Password info on the site.

    The actual username value in the Masked input control gets stuffed into the Element's "data-unmasked" Attribute. The Element's Value is an altered variation of the username with "*" fillers replacing all but the last three characters. The form will not login without the "data-unmasked" Attribute being set.

    It appears I need to:
      - find a way to stuff a new value into an empty Element Attribute,
        ... or ...
      - revert to use of the error prone SendKeys option to actually type the username into the input control character by character.

    HTML source for the relevant input control is below. Assuming all 9s have been input for the Username, note that the "data-unmasked" Attribute now contains the full input Username value (all 9s) and the "value" contains the masked input Username value (* & 9s). The "value" is easy to set. How do I set the "data-unmasked" Attribute in VBA? I would prefer not to use SendKeys, but will if that is the only option.

    =====
    <select name="SSN" tabindex="1" disabled="disabled" class="fs-hidden form-control" id="userId-select" aria-hidden="true" aria-required="true" required="required" data-reset-text="Use a saved username">
        <option value="new">Add/Use another username</option>
    </select>

    <input tabindex="1" class="fs-mask fs-nounmask form-control valid" id="userId-input" aria-hidden="false" aria-required="true" aria-describedby="fs-user-info" required="required" type="text" maxlength="15" value="" autocomplete="off" data-msg-invalid="This field is not properly formatted" data-msg-required="Please enter your username" data-clear="true" data-masklength="-3" data-unmasked="999999999" value="******999">
    </input>

    <input name="SSN" id="hiddenId" type="hidden" maxlength="15" value="999999999">
    =====

    Any hints or tips?

    Tuesday, June 9, 2015 11:11 PM
  • Hi Bandit292,

    >> How do I set the "data-unmasked" Attribute in VBA?
    Based on your description, it seems that you want to set the value of the attribute with VBA? Am I right? If so, since this forum is for Developer discussions and questions involving Microsoft Excel, your issue is more related with operating the IE with VBA, I suggest you go to the forum below for help.

    Reference: http://social.msdn.microsoft.com/Forums/en-US/home?forum=isvvba

    Thanks for your understanding.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, June 11, 2015 9:42 AM
  • OK. VBA is not an integral part of Excel ???
    Thursday, June 11, 2015 10:16 PM
  • Hi Bandit292,

    >> VBA is not an integral part of Excel ???

    Yes, VBA is an integral part of Excel. If you have issues about Excel automation with VBA, like open workbook, fill the range, please feel free to post in this forum. If your issue is related with VBA, but not related with Excel, I will suggest you go to the forum below for help, and there would be more VBA community members to help you.

    Thanks for your understanding.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, June 12, 2015 10:06 AM
  • OK - understood. I already moved the thread. Thanks for all your help to this point.
    Friday, June 12, 2015 5:31 PM