Author Topic: Excel version of Personal Capital and Mint  (Read 6912 times)

musty$

  • 5 O'Clock Shadow
  • *
  • Posts: 8
Excel version of Personal Capital and Mint
« on: November 28, 2013, 07:53:13 AM »
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!




« Last Edit: November 28, 2013, 08:01:39 AM by musty$ »

Le0

  • Stubble
  • **
  • Posts: 215
  • Location: Ontario, Canada
    • My Path to Financial Independence in 2014
Re: Excel version of Personal Capital and Mint
« Reply #1 on: November 28, 2013, 08:01:08 AM »
I'm not sure if VBA is going to be the best code to do this with. But there is nothing stopping you from writing code in another language and then have VBA code to call those functions. That way you can store passwords as long encrypted codes stored in Excel Cells. To put them there you use a different language that handles that stuff better. A language like .net or something that is already on most computers would be best.

I recently called a simple Python Script via VBA. But that's where my expertise on this subject ends.

musty$

  • 5 O'Clock Shadow
  • *
  • Posts: 8
Re: Excel version of Personal Capital and Mint
« Reply #2 on: November 28, 2013, 08:03:37 AM »
Le0,
Thanks that is good to know. I'll look into that.

SnackDog

  • Handlebar Stache
  • *****
  • Posts: 1260
  • Location: Latin America
Re: Excel version of Personal Capital and Mint
« Reply #3 on: November 28, 2013, 08:36:10 AM »
Fancy!

I had the same problem. I use Yodlee, Mint, and PC as each has different strengths.  I consolidated all my investments (and 401K) at Vanguard.  That way, Vanguard can assist with allocation, performance, etc on a total portfolio or individual account basis.  Financial engines (Monte Carlo) sends me a an email every month or two and reminds me my likelihood of hitting my target retirement income based on variables I set.  I also run a giant Excel spreadsheet which lets me forecast future recurring and one-time expenses and incomes so I can get a feel for the relative importance of various variables (retirement date, inflation, investment return).

growingstaches

  • 5 O'Clock Shadow
  • *
  • Posts: 22
Re: Excel version of Personal Capital and Mint
« Reply #4 on: December 01, 2013, 02:46:45 PM »
SnackDog can you elaborate on what Monte Carlo website/software you are using which is emailing you?   I'm curious.

 

Wow, a phone plan for fifteen bucks!