After much digging around for an all-in-one spreadsheet that I could use as a single point of reference, I found myself empty-handed. Either what I found is too difficult, doesn’t have what I need, or was disjointed across multiple spreadsheets.
With some inspiration and spare time, I took some ideas and improved upon existing solutions to finally end up with an all-in-one spreadsheet to fit my needs.
I’m sharing the spreadsheet with my readers because I believe it could prove to be valuable for other people, plus feedback to make the sheet even better would be great.
- Automatically updating charts to give you an indication of your financial health and Net Worth
- Income and Expenses that automatically update based on raw PocketBook transaction data
- FI/RE calculator showing how soon you can retire
- Dynamically updated Portfolio based on the stock tickers of your holdings, with Cryptocurrency support if you install the Crypto Finance add-in
How to Use the Spreadsheet
Here are the instructions for using the spreadsheet; these apply to the PocketBook integration version. If you’re using the manual input version, the only difference is that you need to manually input your Income and Expenses data as step 1.
Before you start, go to File > Make a Copy and save it to your drive so that you can edit the spreadsheet.
1. Import PocketBook transactions
As your first step, you might want to play around with the spreadsheet and see how it works. Once you’ve done that it’s a good idea to delete the dummy transaction data already in the Transactions sheet.
Open Pocketbook and navigate to the Transactions tab, located on the top toolbar of the screen.
On the right of the search bar is a date field, click the field and change the date range to whatever data you’d like to export, in this instance I’m using data from ‘last 30 days’.
As you only need to do this step once a month, for subsequent imports ensure that you’re only pulling the last month’s data.
On the left side of the screen, press Export (CSV) and save it to your PC. Open it and do the following three steps:
- Remove the last five tabs; notes, tags, bank, accountname, accountnumber
- Do a find and replace on the amount column and remove the minuses (-)
- Flip the data so that the newest transactions are at the top by sorting
Copy the data from Excel and open the Google Sheet back up and navigate to the Transactions tab. If this is your first import you’ll want to delete existing data, but if you’re just importing the next month then insert a row above row 2 and paste the new data.
It’s important to know how the Income and Expenses sheets automatically update. Each cell has a formula that takes the name from Column C of the relevant sheet and looks for it in the Transactions tab, it then totals that category for the month and updates the cell.
You can edit the categories on the Income and Expenses sheets, but you also have to add the Category or ensure it’s already in Pocketbook otherwise it won’t be pulling the right data from the Transactions sheet.
After importing the data, your Income and Expenses sheets will automatically update for the month/months and subsequently update the Budget Summary sheet.
2. Add your Assets
The assets page contains the holdings of your portfolio with automatically updating charts. Firstly, you need to change the stock tickers in row 2 to your holdings. This uses Google Finance to automatically pull in the relevant data such as NAV price, daily change etc. Also change how many shares you own.
Unfortunately, it doesn’t look like Australian domiciled shares have as much data available as US domiciled ones.
With certain stock tickers you can pull in data such as expense ratios, YTD% change, monthly change etc. Given that those don’t apply to Australian domiciled shares, I’ve removed those rows out.
If you have Cryptocurrency holdings, you can also pull in some data using the Crypto Finance add-in, for more options you can read their documentation, but it doesn’t look like you can pull in daily price changes. You can, however, pull in % changes.
The cash component is manually entered as there’s no easy way to pull that data in automatically from your bank account and it doesn’t appear that Pocketbook supports exporting bank account balances.
3. Add your Liabilities
The Liabilities page is the most tedious and least exciting of the lot, as it’s mostly manually entered. Again, there’s no easy way to pull the value of your mortgage, HECS debt etc.
On this page, it’ll at least pull in how much you’ve contributed to each from the Expenses sheet and calculate how long it’ll take you to pay off the debt at the current repayment rate.
4. Configure the Scripts and set your Time Triggers
By now you might’ve wondered what the two small columns at the bottom of the Assets and Liabilities page are and why they’re down there.
Those values get automatically printed once a month via scripts with the idea being that the Net Worth page will pull those values in and the rolling chart will automatically update.
All you need to do to get this working is change one value in each of the two scripts and set a time trigger so that the value prints on the first of every month.
To do this navigate to Tools > Script Editor. For both copyAssets.gs and copyLiabilities.gs, you need to change the value of var logKey. You’ll see it has the value ‘1XXp_7joROYlbG1mlJhRfIbUxM9uiI7zrpllZ1wBusGk’ which is the sheet ID.
You can get the sheet ID from the URL, if the URL is https://docs.google.com/spreadsheets/d/11n21B0CDb01uN1ClqrOf9tJ-q_R_xK2AaDnjO38clkY/edit#gid=636621524 then you need copy the bolded part of the URL and paste it as the logKey value.
Run the script so that it pops up asking you to review permissions, allow it permission and move on to the next step. Running it will add a row to the bottom of the Assets or Liabilities sheets (depending which script you run), you might want to delete that row so that the column starts fresh from the first of every month.
Next is to set up the time triggers so that the values of Assets and Liabilities get added once a month. Go to Edit > Current Project’s Triggers, add a trigger for copyAssets and copyLiabilities to be time-driven, on a month timer, for the 1st, at whatever time you prefer. Be sure to save the scripts before you exit.
Once the scripts and triggers are setup, that’s pretty much it. With that, the Net Worth sheet will automatically update without you touching a thing.
I could go on about the Time to FI/RE sheet, but I think it’s pretty straightforward, just enter the values and away you go.
Hopefully, this spreadsheet will be of value to some people and I’m excited to share it with the world. I’d love some feedback as to things that could be improved or changed.
A shoutout to Reddit users /u/wijwijwij, who created some of the original sheets and /u/hrlngrv for assistance with the formulas.