HTML Object Libray: Essentially Creating a Script to Pull Personal Finances From Online Bank Account

[pawest]

Hello VBA and HTML Object Library coders,
I have a cool personal project (at least I think it's cool). I am really into tracking my personal finances and I am using GnuCash to manage all my money and bank accounts as if it were a real business. If interested, GnuCash is free and available at Free Accounting Software | GnuCash ... there is a ton of support behind this gnu project and I've learned more from reading their accounting manuals than I did in my college courses!

Anyways, back to the project. I'm essentially trying to create a script via VBA (don't know any scripting languages) to pull my .QIF banking transaction history every week and I'll manually assign it to the general ledger. With the code that I currently wrote, I'm able to login to my 5/3 account, navigate to the export history window, but I get stuck at trying to choose the right account and enter the dates.

Here are three sites I'm using as references for my code:
VBA Express : Excel - Log In to an Internet Site
Reading web data with VBA from excel using Microsoft HTML Object Library and Microsoft Internet Controls | Menelaos Bakopoulos
InternetExplorer Object

Here's the code I have so far:

'Make sure Microsoft HTML Object Library and Internet Controls are selected from references

Sub IE_login()
    Dim ie As InternetExplorer
    Dim C
    Dim ULogin As Boolean, ieForm
    Dim myPass As String, myUser As String
    Dim historyDate As Date
     
    myUser = "XX loginusername XX"
    myPass = "XX mypassword XX"
    
    historyDate = Now
    historyDate = Format(historyDate, "mm/dd/yyyy")
    
    'Attempting to open IE and login
    Set ie = New InternetExplorer
    ie.Visible = True
    ie.navigate "https://www.53.com/site"
     
    'Loop until ie page is fully loaded
    Do Until ie.ReadyState = READYSTATE_COMPLETE
    Loop
     
    'Look for password Form by finding test "Password"
    For Each ieForm In ie.Document.Forms
        If InStr(ieForm.innerText, "Password") <> 0 Then
            ULogin = True
             'enter details
            ieForm(0).Value = myUser
            ieForm(1).Value = myPass
             'login
            ieForm.submit
            Exit For
        Else
        End If
    Next
    

    'Wait 7 seconds for good pracitce
    Application.Wait DateAdd("s", 7, Now)
    
    'Loop until online banking page is fully loaded
    Do Until ie.ReadyState = READYSTATE_COMPLETE
    Loop
    
    'Attempt to navigate to Export files site
    ie.navigate "https://www.53.com/servlet/efsonline/accounts-export.html"
      
    'Wait 7 seconds for good pracitce
    Application.Wait DateAdd("s", 7, Now)
    
    'Loop until online banking page is fully loaded
    Do Until ie.ReadyState = READYSTATE_COMPLETE
    Loop
    
    ' *************************************************
    ' *************************************************
    ' *****    The rest of the code is where     ******
    ' *****       I beging to have issues        ******
    ' *****    I don't know what to do with      ******
    ' *****      the textfields and dates        ******
    ' *************************************************
    ' *************************************************

    'Look for history From by finding test "Account"
   For Each ieForm In ie.Document.Forms
        If InStr(ieForm.innerText, "Account") <> 0 Then
            
            For Each ieForm.Selectedlist In ieForm
             
                ULogin = True
           
                If ieForm(0).Value = _
                "XX checking account XX" Or _
                "XX savings account XX" Then

                    ieForm(1).Value = historyDate - Day(7)
                    ieForm(2).Value = historyDate
            
                ieForm.submit
                
                    ' then I would have more code here to process a file download
            
                End If
            
            Next ieForm.Selectedlist
    
        End If
    Next  
End Sub

To see what the export history online account interface looks like, go to a google image search and search for "ib‑sc‑internet‑banking‑1.jpg" ... I'll try to insert the picture here:

I was having issues pasting the source code from the website. I can post this later if that would be helpful to figuring out this project (which it seems like it would be). Thanks for your consideration to help!

 

[Norie]

Don't paste the source code from the site, the board won't display it properly.

It's better to see the source code in place and we should be able to do that since the URLs appear to be in the code.

Mind you there are 2 URLs, why is that?

 

[pawest]

Thanks for your reply, Norie.

The first URL is the 5/3 homepage. It is where I login.

The next URL is the "Export" page, where 5/3 users can go to export their account history. I am able to get my code to execute up to loading the account history page, but I cannot get it to choose accounts, choose dates, go to the file selection page, choose a .QIF file, and download. I figured I had to paste my source code because users will not be able to go to the export history page unless they themselves have a 5/3 account and can login and go to the export history page. That may be an issue with potential help.

Thanks and I hope we can get this project working!

 

[Norie]

This is going to be hard to help with because the part you are having problems with is past the login.

Pasting the source code might help a little but it really is better to dee it in place.

 

[pawest]

You're right, Norie. This is going to be tough to solve. But my lease on life is find solutions, not problems.

Below is a screenshot of the 5/3 interface and photobucket images of the javascript that seems, to me, to be critical to solving this project. I can take other shots of the code per your recommendation if there's something else I need to get a screenshot of:

Source Code:

More Source Code:

Even More Source Code:

Hope this works... again, let me know if you need any more source code... I think that should contain everything necessary, though. I'd love to learn how to read this source code in the terms of my project. Look forward to solving this!

 

[Norie]

I'm still intrigued why you are navigating to 2 URLs.

Is there not a button or link you can click to take you to the export files page?

 

[pawest]

Norie,

There is a button I could click on the main login page. I would have to click the applicable "Account Options" green circular button with white triangle, a dropdown menu will appear and I would then click "Export". The reason why I have two URLs is because once I click that export link, it takes me to the export history page I displayed in my previous post. So, while there's a button I could click, there's no button I could click to get me directly to the file download.

.... any further ideas?

 

[Norie]

There might not be a button but there is a link - Export in the dropdown.

Anyway, if navigating to the URL works then that's fine - sometimes, usually due to security issues, it doesn't work.

Well now that I know why you have the 2 URLs and having a look at the source code I don't think there's much help I can offer except to suggest
some methods you could use.

The first thing you probably want to do when you reach the page with the dropdowns is grab references to the dropdown and the textboxes.

You might be able to do that with something like this.

' create reference to page's document 
Set doc = IE.document

' grab reference to account dropdown
Set cmbAccountSelected = doc.getElementsByTagName("select")(0)
' code to pick something from list - hard to tell what code to use

Set txtStartDate = doc.forms(0).all("Export.StartDate")

txtStartDate.Value = "08/01/2012"

Set txtEndDate = doc.forms(0).all("Export.EndDate")

txtEndDate.Value = "08/24/2012"

' form completed, so submit
doc.forms(0).submit

 

[pawest]

Very impressive, Norie. You're code was very successful. With your help, I was able to figure out how to get it to choose an account and enter the dates. There was an issue with submitting a form, however. Here's what I did:

    ' create reference to page's document
    Set doc = ie.document
    
    ' grab reference to account dropdown
    Set cmbaccountselected = doc.getElementsByTagName("select")(0)
        
    cmbaccountselected.Value = "XX blurred account value XX"
    
    Set txtStartDate = doc.forms(0).all("Export.StartDate")
    
    txtStartDate.Value = "08/01/2012"
    
    Set txtEndDate = doc.forms(0).all("Export.EndDate")
    
    txtEndDate.Value = "08/24/2012"
    
    ' form completed, so submit
    ' this doesn't work
    doc.forms(0).submit

Once I try to submit the form, a website loads that says: "

The server is unable to find the requested file . "</pre>I feel like the code is not clicking the "next" button. Rather than using the doc.forms(0).submit technique, could we do something like clicking the next button? It looks around lines 395 and 405 there is some javascript regarding the "next" button... any thoughts?

 

[Norie]

The javascript you refer to does exactly what we are trying to do in the VBA, it submits the form.

You can try and grab the Next link and click it with something like this.

Set lnkNext = doc.forms(0).getElementsByTagName("A")(1)

lnkNext.Click

I don't think the submit part is the problem actually, the form is being submitted after all it's just the results are a bit unexpected.

What I think it might be is that the selection you've made and/or the values you've entered on the form aren't being recognised.