Warning: this post is long and technical. There's some code, but can be glossed over. I'm trying to see if people are interested in a user-friendly, open-sourced version 2.0.
About half a year ago, I found myself with four different financial accounts that held my growing stache. Tired of password pushing, I wrote some VBA (visual basic application) code to macro the webdata into my Excel spreadsheet. I track all my finances with a spreadsheet.
Here's how it works.
1. I have vanguard, tdameritrade, schwab and my company 401K that I can access via the web.
2. The Excel spreadsheet has a userform which is a simple GUI (graphical user interface) with the four accounts and checkboxes. There's one button that executes the web data download.
3. Another Excel spreadsheet with all the passwords and username data must be already opened. This file is encrypted, but Excel's password security is a joke. I use WinZip and AES-256 zip the file.
4. One of checkboxes is checked and click the button starts the code.
5. The VBA code initiates a IE (internet explorer) instance.
'' Set up Internet Explorer
Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = True
Now the code can command the browser to run scripts
6. Each website has its own login page and handshaking to meet the security measures. By trail and error, I wrote code to mimic what a human would do to login, knowing ahead of time, the username and password. I reference the passwords from step 3.
' go through each element of form, load login and password by NAME\
If Left(websitename, 8) = "Vanguard" Then
'' Vanguard WEbsite Specific Login
' Enter User name then hit button
Set myvar = ie.Document.getElementById(loginidtag) ' point to USER name text field
myvar.Value = actualloginid
Wait1Sec
Set myvar = ie.Document.getElementById("login") ' "login" was id of button on Vanguard login page
myvar.Click
Wait1Sec
While ie.ReadyState <> 4: DoEvents: Wend
7. Once the code gets to the personal account homepage, the page with the interesting data is accessed. The code parses the html source page to figure out which link to "click":
' Go to Balances and holdings page
' look for Balances & holdings link (the second link in the page)
i = 0
For Each elmLink In ie.Document.GetElementsbyTagName("a")
If InStr(elmLink.innerText, "Balances & holdings") Then
i = i + 1
If i > 1 Then
elmLink.Click
Wait1Sec
Exit For
End If
End If
Next
8. The data of your stocks and values are all stored in a table. A third party open-sourced VBA addin call RCH is used to parse the table structure to get the table cell of interest.
' VANGUARD Taxable Data Grab
For Each c In Application.Sheets("WebData").Range(VANTAX_R).Cells
' for loop to loop through each cell in NG benefits
''TextBox1.Text = ie.Document.DocumentElement.outerHTML ' DEBUG
c.Value = RCHGetTableCellFromSource(ie.Document.DocumentElement.outerHTML, 7, c.Offset(0, 1).Value)
9. Once all your account data is downloaded to your local spreadsheet file you can:
a. Calculate your net-worth across all your accounts, can be tracked over time
b. Your stocks can be defined to a class, so you know how your assets are allocated
c. Rebalance properly by accounting for all your assets in different institutions
d. Split your assets in taxable and tax-sheltered. I do this because each type is allocated differently. Taxable is money I will spend from early retirement to age 60. and Tax-sheltered is money for "real" retirement after 60. Vanguard holds both taxable and sheltered, so the spreadsheet helps split them apart.
e. Anything you can do with calculating numbers can be done in your spreadsheet! With enough time and commitment, I can setup the spreadsheet to display your finances just like P.C. or Mint, or some other custom format.
I know people are interested in a Excel version of Personal Capital. However, I thought of some issues if I make a free, open-sourced app in Excel.
ISSUES:
1. How to handle the passwords. The #1 reason I stopped using P.C. was I do not trust them as custodian to all my passwords. Its a honey-pot of thousands of client's password. I plan to be retired for 40+ years. Will P.C. even be around then? And how many security breeches through its lifetime? Even having a digital version of your passwords in an encrypted file on your hard drive is vulnerable, but at least not a great honey-pot target.
2. How to handle the login and subsequent clicks to access your data. Every institution has a different login procedure. I'm not going to open accounts just to see their webpage source file. Maybe this can be out-sourced. I wonder how Personal Capital programmers did it.
3. If I make this, give it away, keep it opened sourced, and benefit with donations or web-ads, will I be liable to damages if the code gets hacked? Its not hard to re-write VBA code and trick someone to use the tainted version. Also, this is a macro, so forcing the user to enable Excel macros is another "can of worms".
MMM once wrote that 10% of the software engineering population clicks his website. Any of you software gurus can chime in?
Thanks!