|
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
|
|
|
|
|
There were no inputs to my question but (if anyone cares) I did solve this issue with help from some other developers.
1.) Some web sites (Fidelity included) mask the USERNAME and/or PASSWORD user input. If you drill into the source code for these style of HTML INPUT objects, they will have dual <INPUT> ... </INPUT> lines for the same object. One will be unmasked and the other masked. Choose the INPUT object ID defined in the masked input line to pass a value to the object.
2.) My QueryTable.Add line from above was also causing erors. Upon further review of the QueryTable object model, the required RANGE parameter "must be on the worksheet that contains the QueryTable object". I was attempting to add the QueryTable to the MAIN worksheet and extract data to individual DATA worksheets. I revised the code to add the web query to each individual DATA worksheet.
Adios
|
|
|
|
|
My bad - I spoke too soon about solving the login problem.
When my solution above 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.
Any hints or tips?
|
|
|
|
|
Hi there!
I'm using the HTMLTextbox from here: [^]
And my Question is how can I change the Font, Fontsize, ....
My Usage:
Private Sub Message_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Message.TextChanged
MessageHTML.Text = Me.Message.GenerateHTML
End Sub
as you can see, everytime my Text in Message is changed my HTML code will be generated, and it works great!
For changing the Font I use:
Private Sub FontBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FontBox.SelectedIndexChanged
Message.SelectionFont = New Font(FontBox.Text, FontSize.Text.ToString)
End Sub
And for the Size the same:
Private Sub FontSize_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FontSize.SelectedIndexChanged
Message.SelectionFont = New Font(FontBox.Text, FontSize.Text.ToString)
End Sub
But I don't know how to update my MessageHTML Textfield.
Really hope someone can help!
Thanks
|
|
|
|
|
If you have a question about code from an article, use the forum at the bottom of that article. The author of the code is the person most likely to be able to help you with the code they've written. 
HTML Text Box Control ⇒ Comments[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
In an existing vb.2010 desktop application, I need to have the users select the vendors they work with plus the access 2013 database file they want to use as input into the application. I need to have the users select these options before they do their normal processing.
Thus to add this feature to the application, which would you do:
- Have a new form setup with only these features, and once these options are selected, have the application then call the existing form that starts the application right now?
- Would you add these selections to the existing startup form, and 'gray out' the options the users are used to having? Once the users pick new selections, then enable the original options to work?
Thus can tell me the following:
- Which option you chose and why,
- Let me know if can you think of other options that would be good to use in this situation?
- Show me the code and/or point me to references that will tell me how to accomplish my goal using the option you selected?
|
|
|
|
|
dcof wrote: Which option you chose and why,
Option 2.
Create a form and only create checkboxes that apply for that user. Disabled checkboxes imply that they can be unlocked.
..and they can. Using the "inspect" application from the Windows SDK.
dcof wrote: Show me the code and/or point me to references that will tell me how to
accomplish my goal using the option you selected?
Any tutorial that shows how to create controls dynamically and add them to a form. MSDN has them, CodeProject has them, Google knows them.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
dcof wrote: Which option you chose and why
I would subscribe to the Shown-event of the "main window" and show the setup form modally there (by using .ShowDialog() instead of just .Show()) which will prevent input to the "main window" while it's being shown. Why: Because it's the easiest way:
Public Class Form1
Public Sub New()
InitializeComponent()
AddHandler Me.Shown, AddressOf Form1_Shown
End Sub
Private Sub Form1_Shown(sender As Object, e As EventArgs)
Application.DoEvents()
Dim form2 As New Form2()
form2.ShowDialog()
Dim form3 As New Form3()
form3.ShowDialog()
End Sub
End Class
Explanation for Application.DoEvents() in this article:
Immediate display of WinForms using the Shown() event[^]
edit: Just realized I misinterpreted your question slightly. Regarding the actual question, whether to show 1 or 2 "setup forms": I would go for two sequential setup forms. It's more intuitive to the user.
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
modified 4-Jun-15 17:04pm.
|
|
|
|
|
Thank you for your answers so far! However I do have the following additional questions to ask you:
- If I create a new form as you suggested, this new form will now be the first form that a user sees. Thus I would set the new form as the startup correct? If so how do you change the startup project? If not, how would I 'wire' this form into the application so that it displays before the current startup form?
- What should I do to make the 'current' startup form display after the user clicks the button on the new modal form? Basically how do I have the new form call the existing startup form?
- On the existing startup form, is there any kind of logic that I would need to change since it is not the startup form any longer? If so would you let me what I need to change and how would I make the applicable coding changes?
|
|
|
|
|
The application surely has some kind of "main form" which is shown after the "setup form" has been confirmed, right? The code I've shown in my previous message would be an excerpt from the "main form" class. The main form loads and becomes visible but immediately shows the first of the two setup forms and after that has been confirmed, the second setup form will be shown. So, if the current setup form is set as the "startup form" in your project settings, you would change that to the main form instead and incorporate my code example to show the setup forms "from within" the main form.
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
|
In a Visual Basic Net 2010 desktop application, I want users to be able to select only of the Access 2013 database files that are located in a network share area. I would like the Access 2013 file names to be in a dropdown list box for the user to be able to select from. I would also like there to be a default Access 2013 file that will be used.
Thus can you show me the code and/or point me to a url (link) that will tell me how to accomplish this goal?
|
|
|
|
|
|
Would you show me the code and/or point me to url(s) or link(s) that give more detailed code examples how on to accomplish the task I am asking about?
|
|
|
|
|
The first link shows how to get a list of (Access) files. The second link how to work with a listbox.
It is general documentation.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hello !
I've used this code to open a form :
Dim frm1 As New Myform1
frm1.Show(Me)
How can I do the same thing , when I doesn't know the form's name at design , but only on runtime.
I have a variable that contains the form's name as string :
Dim frmname as string
What can I do ?
Thank you !
|
|
|
|
|
You can use reflection to search for the Type of the Form and then create an instance of it with Activator (Activator has bad performance compared to other methods but for Form-instantiation it won't matter):
Dim formName As String = "Form1"
Dim formType As Type = Assembly.GetExecutingAssembly().GetTypes().[Single](Function(t) t.Name = formName)
Dim form As Form = DirectCast(Activator.CreateInstance(formType), Form)
In case the Form-class is defined in a different assembly than the assembly in which that code is executing you would have to replace GetExecutingAssembly():
Dim formType As Type = Assembly.GetAssembly(someType).GetTypes().[Single](Function(t) t.Name = formName)
..where "someType" is any Type (static notation) that is defined in the same assembly as the Form.
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
|
Thank you friend !
This is working , but on my question I forget something that I should do :
On my original code ( when i know the Form), before I call the
frm1.Show(Me)
I have this instruction :
frm1.MyButton1.Enabled=True
Now , if I use your code , this instruction doesn't work.
Shows an error :
Mybutton1 is not a member of System.Windows.forms.Form
What can I do ?
Thank you !
|
|
|
|
|
Do all Forms which you will create through reflection have such a button?
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
|
|
Then I would pass an "enableButton"-flag as constructor argument to the forms. Activator.CreateInstance(..) has an overload where you can specify an array for the constructor arguments as second argument after the type argument:
Dim enableButton As Boolean = True
Dim form As Form = DirectCast(Activator.CreateInstance(formType, New Object() {enableButton}), Form)
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
|
ok , but there are cases when I need to access a control on this form , and this control also is a variable , and its name is known only on runtime.
Is there any way to do this ?
|
|
|
|
|
How would you know which control has to be accessed?
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
|
the control name is generated in a string variable.
I just want to get this control , and after I just want to use the enabled property ( so doesn't matter what kind of control is because all have the enabled property ).
But I don't know how to get this control by the name as a string.
|
|
|
|
|
By control name do you mean the member name like MyForm.SomeControl or the Name-property of the control MyForm.SomeControl.Name ?
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
|
I mean I have a variable with the control's name for example
cname="MyTextBox1"
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
