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.

Monday's Meowment: Kitty Mural

Monday's Moment: Classic Land Rover by Al Zubarah Fort

Monday's Moment: Happy Mother's Day from Baby Camel

Monday's Mermaid Moment

Monday's Moment: Call Sign Kitty

The arrival of our tween

So back in February, Clarissa asked if she could have her birthday sleepover party early. I said no, your birthday isn't until April. Then COVID-19 happened and everything got shut down! Clarissa kindly pointed out, I should have let her do her sleepover party back in February....touché little grasshopper! So how does one make a birthday special during a time of social distancing and only in person interactions are with your family?!? Well....I finally made the birthday bunting to decorate with and the previously mentioned fabric gift bags.

I had to tell someone I counted how many times I wrapped the ribbon around the bags to prevent them from sneaking a peek!

A couple days before her birthday, I sent out an invite to her class, scout troop, and some other friends she plays with in the neighborhood.

The googles suggested we "play games" and deliver activity kits of things to do together during the party. Well most compounds (including ours) are shut down to visitors, so logistically delivering activity kits wasn't going to happen. There wasn't really any suggestions of games either....so brainstorming I did. I then gave Clarissa my ideas....one was thumbs down (no dance party with music) and two were given the okay (mermaid names, emoji game)....so I thought some more....and came up with a drawing game...got another thumbs up. So while there wasn't a lot of decorating, food prep, and clean up....there was other prep I had to do!

So first up, was the Mermaid Name activity. So I made this mermaid naming guide. There are bunch online. Clarissa and I chose the names we liked from different ones, made up some of our own names, and put it all together. As people came onto the zoom call, they could change their display name to the mermaid name. Since there are a few boys in her class, we also include a merman name one (but since I didn't make it, I'm not going to post it, you can find it here). I also posted this on Instagram/Facebook with a message to reply back with your mermaid name. We've had a ton of fun reading the mermaid names of family and friends. Clarissa thought all the name combinations were so pretty!

Once most everyone had said hi/their name had been updated, we played an emoji game. So I did a 'share my screen' and started a powerpoint. I would pop up an emoji combo like this

Then once we heard a bunch of kids yelling 'starfish', I would click the answer. Then move onto the next one....want to quiz yourself?!? I'll make a separate blog post with the whole game (because what else do we have going on right now?!?!?). I also know my dad really enjoys mind games like this....so I hope he gets a kick out of it, since I know he's one of our readers!!

After the Emoji game we did a drawing game. When I sent the zoom link to everyone, I asked them to bring their dry erase board/marker or a pen/paper. So while I know there are online games that can be played that everyone draws/guesses. Logistically, that was too much with a bunch of 8-10 year olds! So I made a list of words before hand. I put everyone one on gallery view. I would have Clarissa leave the room/close her eyes. Then I would show everyone the word. They would all draw the word and Clarissa would have to guess based on everyone's drawings. There was only a couple words that stumped her.

Afterwards, one of the mom's from the class messaged me and said she loved the games and thought they were great. The kids were all laughing and participating. So I would go with a win! The only hiccup was I overestimated how long things would take....so we finished a bit sooner than planned. It's hard keeping a party momentum going in person...FYI....even harder on a zoom call...lol...we had a decent turn out too!

The last drawing word was Birthday Cake.....so of course I pulled out one of the two cakes I had made. The kids all sang and Clarissa blew out the candles. Some kids left then and others stayed and chatted. There was a bit of a lull and then a few of the kids started playing Roblox together online...which was apparently amazing and they started discussing plans to do that everyday via zoom. So, I'd call it successful zoom party!

Clarissa had requested an ice cream cake....so I split the ingredients and made it in two pans....for both her birthday events! When Richard got home for work, we opted to open her family gifts as dinner was starting to cook. First up, grandma and grandpa's gift (who were actually on the telephone during the opening of their gift).

Per my suggestion. Someone loves painting and watching squishy make-over videos on YouTube (I don't understand it either)....I had actually put in the order to help out the grandparents prior to all the shutdowns associated with COVID-19...but this is actually the perfect gift as it something to work on that will take time....which we have A LOT of!!

Next up, the teen got Clarissa a Moana Funko-Pop which she liked and then my hand-me down birthday angel. She loves to read the little poem each year that is attached to it.

Then the giant gift, which she seemed to have forgotten about picking out the color for from Richard and I....her new scooter...which is perfect for playing outside with her friends....oh wait....ok, perfect for taking rides with mom and dad... Then the IOU for a shopping spree with Nana and Bajoo! (Clarissa had already FaceTimed with Nana & Bajoo mid-afternoon....this girl had a busy virtual social calendar on her birthday!).

Clarissa requested my fettuccine alfredo (I have a really easy but light homemade sauce) for dinner. While I was finishing up dinner I set the zoom call with my sister-in-law....then a genius idea hit me....I did some sleuthing and sent an email. I got a quick response back. I got some sass at the table about having my phone out, but I said I was working on a surprise.....at 7 pm.....viola!

I got the whole gang on there....including Busia....Clarissa's 101 year old great grandmother!! The retirement community she lives in was wonderful at helping out (hence me checking my email and sending messages at dinner)! They didn't turn on video but she was able to see all of us...now that I think about it....it's probably because my cousin hasn't been able to get over there to do Busia's hair in a while...lol. I was going for it to be surprise for Clarissa, but I think it ended up making Busia's day. Since all the COVID-19 stuff has happened, understandably no visitors are allowed in and the residents stay in their individual apartments (no community dining/events). So she's been bored...so to see all our faces was great. So as you can see....they all sang happy birthday to Clarissa while she got her second cake of the day.

And for all of you at home....this is what Clarissa's view was of all the excitement!

There was even some happy birthday signs from our zoom guests (IDK why my mouth was open...)!

So while not the sleepover she wanted...we made due and still had a fun birthday!