TSP & Quicken 2019

So as you know, we typically take R&R during the summer (this summer due to C-19 it is still TBD).....but given all the extra 'stay at home' time Richard and I finally tackled something we'd been putting off for a while....and it ended up not being all that difficult. I've decided to share our 'imperfect work around' to the TSP and Quicken problem. For those not in the know, TSP is the US Federal employee retirement program - it stands for Thrift Savings Plan and is the 401(k) equivalent for civil service and military. 

We have been using Quicken for a while and recently upgraded to Quicken for Mac 2019 with the online/mobile features (so if we ever do go on R&R, I can keep things up to date while gone for a month or two by updating it online, then when I get back home the desktop version will sync with everything I did online and I won't spend days playing catch up). Unfortunately, Quicken and TSP don't play nice (aka unlike other investment accounts that will automatically download information). This has been a common problem for a while (from what we read, it is something to do with paying for fees/allowing software access and TSP just doesn't have time to bother with Quicken). We found some stuff about downloading CVS files and importing but everything we found either had to with the Windows version of Quicken, really old versions of Quicken, or the links were dead. So we finally figured out a work around - it's not perfect - we use the end of quarter data versus using live or transaction by transaction data, but it gives us a general idea as to what's going on in the account - something we haven't had for the last 7 years....so I'd say that is a much greater improvement. So I'm going to stop babbling about it and show you what we did. 

1. Log in to your TSP account. Go to Statements. Download all your Quarterly/Annual Statements (I found it was easier to download than view. If you take too long, you tend to get booted off and have to do the whole log in process again). I gave the statements easy names like 2013 Q1, 2013 Q2, etc. We did Richard's first and then mine (I only worked as CLO a bit so I only have a few statements).

Once they were downloaded, I took all the end of quarter information and entered it into excel. I'll figure out how to share a blank copy of this excel sheet, no need to re-invent the wheel. I've circle the values I took from the statement. Only the first statement would you need the beginning number of shares (which should be 0). All future statements you will only need the Ending Balance numbers as I have Excel calculating the Starting Shares for you (but you can verify the number if you want).

For each statement, I entered the End Share Price and the End # of Shares for each Fund we have any Investments in. Excel then calculated the # of shares bought (end - start) and the total cost (shares bought x end share price). I'm aware Total Cost is like Dollar Balance on the statement but you need all the extra decimal places for Quicken to calculate the Share Price properly. 

All my undergrad/grad school data entry skills are still sharp. This part took only an hour or so, I spent more time opening pdf's than entering numbers. Now for getting this into Quicken. It's possible we could have made a cvs file and done it one step, but I probably would have spent longer figuring out how to do that then just entering it. 

So now into Quicken for Mac we go. First thing we need to do is make our TSP Account. So go up to the top, click the + for 'Add Account', then select Investments, then select 401(k).

At this point, a screen pops up asking you to enter your bank name. You can try to search TSP or Thrift Savings Plan, but it won't find them (unless something has changed and after reading this post they felt bad and are now playing nice). So save yourself the time and click the 'My bank is not in the list' and then 'Add Manual Account' on the next pop up.

At which point you will be presented with an Add Opening Balance info screen. Enter your start date and balance, then click finish. 

Your account is made. You can click the little settings button on the bottom right hand side and change the name of your account (must be on transaction entry format - to switch just click on transaction or portfolio below the name).

Now to start entering your quarterly contributions. Click the 'Add Button' on the bottom left side (again must be on the transaction entry format). Enter the date the quarter ended, then select 'Add Shares'. The format will change to what you see below. Change the date of acquired to match date the quarter ended. 

For Security add the TSP fund. The first time we did this, we had to add our own (as they aren't in the system and we are updating it all manually). After that, we had a nice drop down list. 

Then you enter total cost and number of shares. I would always check that the Quicken calculated Price per Share matched the Statement Price per Share on the Excel spreadsheet (a little data verification while entering). Then Save.

Once everything is entered you will see them listed under the transactions.

You can then switch to the Portfolio format to view how everything looks together. Here's just the sample one.

Here is Richard's pretty portfolio graphic. All in all, I think it took us 2-3 hours to enter all 7 years worth of 5 funds worth of data (we split it up over 2 days and took turns entering/reading off really long ##.##### numbers). When you're stuck at home....it's a "fun" project to suddenly get a pretty graphic that looks like this! 

Now you might be wondering, how can you see that C-19 March 2020 stock market dip and the start of the recovery if you are only entering quarterly data? You can add closing price data anytime you want....daily even. So if you log in to your TSP account. You can see what the share price is on any given day. Select 'Account Balance' on the side bar and after that screen opens, you can adjust the date at the top You can also go to this website which posts TSP data without being logged into your account!!

You can then take these share prices and input them into Quicken. While on Portfolio view, click on the share who's price you want to update and it brings up this option window. Select 'Edit Security'. After you select that, it brings up this next screen. Click on 'Price History'.

In the Price History, you will add a new price History. Enter the date and the Closing Price you got off the TSP 'share price for the day' page. Then click the Add button. 

Just like that you will have pretty wiggles in your TSP Investment profile graphic. We've decided Richard will log on to TSP on Saturday's and get the closing price on Friday's and I will update Quicken. A nifty feature is if I update the price history for his L2040, it automatically updates the price history for my L2040, etc. For this, we have a little spreadsheet (we've put it on google sheets to share between us since we have different computer profiles).

From this point on, it will be easy to quarterly continue to update the share values as well the new shares acquired. It should only take a few minutes to keep on top of it. 

That ladies and gentleman is how a Marine Biologist and a Political Science major outwitted the software!

If you want to do this for your own investment account, feel free to use our spreadsheets. For the Spreadsheets, they are now in Google Sheets (similar to Excel). Click here to download.