Tag: excel

Downloading Fidelity 401k transactions into an Excel spreadsheet

The Fidelity 401k account transactions download option just lets you download amount/shares per transaction – i.e., they don’t give you the option to download the split of each transaction (employee pretax, employer match, after tax etc.). I maintain an excel spreadsheet of my own for this but it’s a pain as it needs to be kept up to date manually by looking at the Fidelity website (and clicking the pesky ‘show details’ link to expand each transaction).

So I automated a bunch of this – you can get the scripts from https://github.com/amalani/FidelityTransactions.

  • Copy pasting the first file (fidelity.js) in the F12/developer options’ console tab of the browser when you’re on the transaction history page of Fidelity downloads all the transactions into a new file (transactions.js) on your machine.
  • Dropping this file on the second file (ExportToCSV.js) in Windows Explorer generates a CSV file containing all the transactions which can then be opened in MS Excel.

The CSV looks something like this but MS Excel treats it as a spreadsheet.
Date,Investment,TransactionType,Amount,Shares,Blank,01 - EMPLOYEE PRE-TAX,02 - EMPLOYER MATCH,05 - REGULAR AFTER-TAX,
12/31/2015,FIMM MONEY MKT INST,Contribution,1094.79,1094.790,,,60.08,1034.71
02/02/2016,VANG S&P 500 IDX TR,Exchange In,1000.00,9.82,,764,236
02/03/2016,FIMM MONEY MKT INST,Dividends,0.43,0.430,,0.25,0.18

Let me know if you find it useful.

Monthly/Annual Cash flow spreadsheet

I’m a big fan of using MS Excel to do calculations, plan and budget. If you’re planning a yearly budget, it helps to make a list of income vs major expenses on a rolling 12 month period to see whether you expect the cash in your bank accounts to cover anticipated expenses.

Here’s a sample cash flow spreadsheet for a couple and their child. I’ve come up with some random numbers and a scenario where the couple doesn’t work (or is on unpaid leave) for a month or two (and thus doesn’t get a paycheck) and some estimated expenses to illustrate how to use the spreadsheet.

This is what it looks like – preview link.

The yellow background cells are the ones that need to be filled. Blue indicates calculations arising from the spreadsheet. The red cells are alerts to watch out for. The last row indicates the amount expected to be in your bank account at the end of the month.

To use this spreadsheet, go to this link and click File -> Make a copy to make a copy of this in your Google account (you’ll have a to login for this). Then you can make changes to the numbers in your own copy of the sheet. Keep coming back to the sheet and check the version history tab to see if I have any more changes than what were there in your copy.

Let me know if you’d like any additional features in this spread sheet.