FAQs
A full list of FAQs related to the sheet. Have one that isn't answered here? Ask in the Subreddit!
Using the Sheet
How do I enter my own historical data into a new Sheet?
To enter your own historical data into the Sheet, follow the instructions below:
Head to the History tab
Starting in Cell A3 and working down, enter in the dates (ie. 1/6/2020, 1/7/2020 etc.) for the previous data you want in the sheet. Don't worry about the months already in the sheet, they will automatically move down.
For the new months that you've entered, from left-right fill out the values in the blue columns.
How do I know my data is kept private?
A very valid question! The Sheet has been designed to be completely self-contained. When you download the Sheet and make a copy, it's now it's own independent copy hosted by you. The external services permission is triggered because the Sheet uses live Crypto, Stock and Managed Fund prices which requires the sheet to fetch external prices from Google Finance, Yahoo Finance & Bloomberg. You can review all the formulas to see how that works, but it's strictly a one way fetch process and if you go Tools -> Script Editor in the Document you can review all the code yourself to make sure it checks out. Furthermore, no personally identifying information or sensitive information such as passwords/usernames are needed nor requested. The only inputs into this Sheet are holdings and amounts, the minimum information needed to determine your net worth & associated performance. Furthermore, there are 2 versions of the sheet - Full and Slimmed. The Full version contains all the features on offer in the Sheet, but as such requires some additional permissions to complete these tasks. The Slimmed version has been designed to be more private and self-contained, but as a trade off has had some features removed to not require additional permissions. See the below FAQ for more information. You will receive a link to both versions to download in your email invite.
What is the difference between the Full and Slimmed Versions?
There are 2 sub-versions of the Complete v2 Sheet that you can download, and these differ to offer varying levels of privacy and feature sets. The Full version contains all the features on offer in the Sheet, but as such requires some additional permissions to complete these tasks. The Slimmed version has been designed to be more private and self-contained, but as a trade off has had some features removed to not require additional permissions. You will receive a link to both versions to download in your email invite. Full Version Permissions:
See, edit, create, and delete all your Google Sheets spreadsheets - To allow the Migration tool to automatically copy your data across from an older Sheet version when you upgrade to a newer version.
Connect to an external service - To allow the fetching of live Stock/Crypto/ETF prices from sources such as MorningStar, Yahoo, FT.com and CoinMarketCap where GoogleFinance does not provide a live price.
Send email as you - To send you a record of your end of month progress
Display and run third-party web content in prompts and sidebars inside Google applications - To display the end of month summary prompt in your Google Sheet (same content as the Email)
Slimmed Version Permissions:
View and manage spreadsheets that this application has been installed in - To allow the script to process your monthly numbers and then write them back to the Sheet as history in the History Tab
Connect to an external service - To allow the fetching of live Stock/Crypto/ETF prices from sources such as MorningStar, Yahoo, FT.com and CoinMarketCap where GoogleFinance does not provide a live price.
Display and run third-party web content in prompts and sidebars inside Google applications - To display the end of month summary prompt in your Google Sheet
Missing features - No migration tool (if the destination sheet), monthly email updates or calender invites/prompts.
What region of the Sheet should I pick?
It is recommended that you choose the Sheet region that matches your region due to retirement/tax differences, or alternatively if you are earning internationally the currency you are paid in.
When do I press record for the month?
When you record a month (ie. January) you can only record it while inside that month (ie. between Jan 1-31). In terms of when it's best to run inside the month, this is ultimately flexible to your needs but it is heavily recommended that you run the Sheet on the 1st day of the month. This is so that if you forget and are a few days late with entering data you can do so a few days later while still being inside the month. If you record on the last day of the month (ie. 31st January) and forget and run it the next day (1st Feburary, the Sheet will think you're now trying to update values for Feburary.
I accidently clicked the button to end the month too early and hadn't updated all my assets, what do I do?
If you accidently ended the month too early it's extremely easy to undo. Simply follow these steps:
Click File -> Version History -> See Version History
In the right Sidebar, click on the dated version of the Sheet before you clicked to end the month
At the top of the screen, click the green 'Restore this version' button
Finish off entering your data and run the Sheet again
A new Sheet update is released, how do I upgrade to a new Sheet?
How do I migrate data between sheets?
As of v2.9 the new Automated Migration Tool will now handle all Sheet migrations and updates. See instructions on how to upgrade/migrate your sheet here.
How do I enter in a missed month?
To backfill data for missed months, follow the below:
In the History tab in Column A, enter in the date you want to backfill in the correct ordering compared to your existing months. If this is your first time running the Sheet, simply write in the date missed into Cell A3.
NOTE: Do not Insert a new row. Simply make use of the rows already in the Sheet.
Proceed filling out the Asset values for the new month, updating the blue columns only.
Done! The Date Column should automatically adapt and the current month should automatically shift down.
Can I use the Sheet with Excel, Offline or Libre Office?
Unfortunately no, as the Sheet is heavily reliant on Google Sheet functions for live pricing, custom Google formulas and most importantly the behind the scenes scripts written in Google Apps Scripts. You may download the Sheet and try importing into Excel, but you will find that significant elements of the Sheet will not work and it is therefore heavily not recommended. If your concern is privacy, please see the FAQ entry How do I know my Data is kept private?
A new version is released but I haven't received an invite email, what do I do?
Please click here to send you the latest invite.
Often due to the keywords in release emails they can be marked as spam or placed in your Promotions Gmail sub-inbox. To stop this happening in the future, please add to your contacts my email address (the same email that you received on the original invite) to stop future emails being filtered. If you have not received an invite please fill out the above link, post in the official Subreddit, email me or contact me via Reddit. Please also note that only major releases are announced via email. Minor releases are published to the original link of the major release and are unannounced. To keep tabs on minor releases, please see the live changelog here.
I haven't received my Sheet invite, what do I do?
Please fill out the form here.
How do I use the tool if I'm paid fortnightly?
First off, in the SheetOptions tab set your pay Cycle to fornightly and update your fornightly pay value. This will help the Template understand your monthly pay. When it comes to the Sheet however, all calculations are strictly performed in the month timescale. This is to enable a lot of date functions to work on a standardized timescale. So tracking and statistics will be performed at the monthly time scale.
The dates in my Sheets keep ticking over and my history isn't being recorded, why is this?
To record the month of progress that you're in, it is critically important that you press the Red Update Button in the Net Worth Tab at least once a month (pictured below).
This is so that you can enter in all your up to date values before pressing the button to record the month. If you do not press this button, the Sheet will keep ticking over to the next month until you update your Net Worth for the first time. Please note:
It is recommended that you update your Net Worth on the 1st of every month.
You must always record a month while in that month. ie. You can only record November while inside November.
What are all the Sheet permissions for?
Here is a breakdown of the permissions needed by the v2 Sheet and their uses: Full Version Permissions:
See, edit, create, and delete all your Google Sheets spreadsheets - To allow the Migration tool to automatically copy your data across from an older Sheet version when you upgrade to a newer version.
Connect to an external service - To allow the fetching of live Stock/Crypto/ETF prices from sources such as MorningStar, Yahoo, FT.com and CoinMarketCap where GoogleFinance does not provide a live price.
Send email as you - To send you a record of your end of month progress
Display and run third-party web content in prompts and sidebars inside Google applications - To display the end of month summary prompt in your Google Sheet (same content as the Email)
Slimmed Version Permissions:
View and manage spreadsheets that this application has been installed in - To allow the script to process your monthly numbers and then write them back to the Sheet as history in the History Tab
Connect to an external service - To allow the fetching of live Stock/Crypto/ETF prices from sources such as MorningStar, Yahoo, FT.com and CoinMarketCap where GoogleFinance does not provide a live price.
Display and run third-party web content in prompts and sidebars inside Google applications - To display the end of month summary prompt in your Google Sheet
Missing features - No migration tool (if the destination sheet), monthly email updates or calender invites/prompts.
Where can I see a changelog of the Sheet?
You can view the Sheet changelog here.
Can I make the Sheet into more of an app?
For those running a Chromium browser (ie. Google Chrome, Microsoft Edge, Brave etc) you have the ability to package and view your spreadsheet as a pseudo app. To do so follow the instructions below:
Open your Spreadsheet and open the tab that you want to land on (ie. Net Worth)
Click the Browser menu button in the top right hand corner (3 dots/lines)
For Chrome: More Tools -> Create Shortcut, for Edge: Apps -> Install this site as an app
Name your Shortcut
Pin this to the taskbar or your Desktop.
Please note you will have to again setup this up for any new Sheet upgrades in the future as every new Sheet has a unique URL.
I've become a Patron, how do I get my Subreddit flair?
To get your unique r/CSPersonalFinance Patron Supporter flair, please click here. If your flair does not show up within 2hrs please contact me via Patron and I'll manually add it for you. Thanks for your support!
When I migrate the Sheet I get an "Exceeded maximum execution time" error
This can sometimes happen if there is a particularly complex/large amount of data to migrate.
It is recommended that you set only half the migration options to "Yes" at a time, and migrate each half separately. If this doesn't work, reduce the amount of options set to "Yes" and migrate them all separately.
You can click the migrate as many times as needed for it to complete successfully. There's no harm, the tool will just overwrite what was migrated previously each time.
When I try and initialise I get a ‘This app is blocked’ error, what should I do?
This is an obscure error that may trigger on some Google account configurations. This error is caused by a Google security setting designed to protect you against malicious and unauthorised scripts (a very good thing!). It is caused when you are trying to run a script that has been sourced from the internet which hasn’t been verified and published under a Google Sheet app. Only a small percentage of users will see this message. As part of the way I approached this Sheet, I designed all the scripts to be self-contained and packaged with the Sheet for full transparency. This is so what if you wanted to audit the Sheet, you could go Tools -> Script Editor and verify what the code is doing yourself and have full transparency at any time. My personal believe is this is the only way to have a trusted and secure environment. Personally, published Google Sheet apps are a bit of a black box as the code cannot be reviewed and audited by yourself. So this error is caused by a good security feature, and an approach to transparency. To fix this error, you can:
Create a seperate, dummy Google account and run the Sheet under that user - This in most cases fixes the problem.
Turn off Advanced Account protection (not recommended) - This is the security feature that can in some configurations trigger. It is recommended that you don’t turn this off as it is a necessary security feature, but you can if you want to run the sheet under your main account.
Please see the FAQ entry on Data privacy if you have any further questions in this area.
Where can I see the latest changes in the Sheet/Change log?
Thanks for your offer to help and improve the Sheet! You can sign up for future Beta tests here.
What does CompiledSanity stand for?
CompiledSanity is the Reddit username of the creator of this Sheet, and the name by which the Sheet became known by when I first started sharing the Sheet on the internet. While in hindsight I wish I could have named it something more approachable and easier on the eyes, by this time it was too late and the Sheet had become synonymous with 'CompiledSanity'. The words themselves draw from programming workflows and some frustration I had with a particular task at the time. I may still change the name in the future, but for now it is best found on the internet by the name the Sheet is known by.
Personal Circumstances
I'm paid irreguarly or with irregular amounts, how do I account for that?
2 ways. Either:
Enter in an average figure that you are comfortable with
b) Set your Salary to $0 and enter all your income into the Side Income tab. This was designed for irregular amounts and will automatically feed an averaged value into the rest of the Template.
How do I enter and account for my Mortgage Offset Account?
Mortgage Offset accounts can be dealt with in many different ways. Uniquely, Offset accounts can count as both a Cash account as well as downpayments on your loan. Since this money can flex in between it can be a grey area, but for the purposes of the Sheet it needs to be more definite. It is recommended that you come up with a theoretical minimum floor/emergency Cash amount that you have earmarked for emergencies, investments or general purchases ($30,000 for example). This $30,000 balance would go in the Cash tab as 'Offset Account'. In the Property tab for 'Mortgage amount paid' you would put the remainder of this balance (ie. Total Offset Balance - $30,000). That way as your Offset Cash balance grows, in the Sheet more of your mortgage will be reflected as paid off over time.
How do I account for a Mortgage with both a fixed and variable component?
Mortgages with both a fixed and variable component need to be broken down into 2 separate mortgages in the Property tab. Set monthly amount paid of each loan to 50% of your total contribution each.
How can I use my investments in my Retirement/Pension balance?
How does the Sheet account for multiple currencies?
A key feature of the Sheet is the automatic and live handling of multiple currencies on the fly. Assets can be priced in overseas currencies, and then converted back to the Sheet base currency for comparisons sake. To do so you need to do the below:
Set the 'Base' currency of the Sheet, or what you want everything to be priced back to. It is recommended choosing a Sheet that is closest to your base currency, so AU version for AUD or EU version for Euro for example.
In each of the tabs (excluding Property which doesn't have international support yet) enter in the currency of where your item is priced.
For example, as a person living in Australia holding some Shares of Apple in the US, with a $20,000 US Bank account and a $200k USD house.:
Download the AU Sheet version
Set Sheet Base Currency to AUD (SheetOptions Tab, cell L24).
In the Stocks Tab, enter in 'NASDAQ:AAPL' in the Ticker Column and 'USD' in the currency column as this particular stock is always priced in USD. The Sheet can't always determine what the currency is.
In the Cash tab, enter in the $20,000USD balance in Column C in the top left. Enter in the USD as the currency in Column B.
As the Property tab doesn't yet handle international currencies, you'll have to preconvert all your values (ie. $200,000USD Purchase price into $260,461AUD Purchase Price) before entering them into the Sheet. You'll then have to keep up to date the Current Price based on current exchange rates.
Supported Assets are:
Cash - Guide here
ETFs - Guide here
Shares - Guide here
Managed Funds - Guide here
Crypto - Automatically converted
Other Assets - Guide here
Retirement Accounts ported from Managed Funds tab - Guide here
How do I track with my partner?
For couples it is recommended that you combine finances and incomes as expenses will likely be incurred as a household and not for individuals and therefore Net Worth changes need to be considered as a whole. A key point when combining finances is the Tax rate input in the Sheet Options tab. For a couple in different tax brackets a weighted averaged will needs to be used. Please use the Couples Tax Calculator (bottom left corner of SheetOptions tab in v2) to find your averaged tax rate.
Does the Sheet automatically sync with my Bank account?
Not currently, but this is planned for a future version.
How do I enter my Credit Card into the Sheet?
There are 2 ways to handle inputting Credit Cards:
If you pay off your Credit cards each month - Enter your credit card balance on the cash tab as a negative number. As this isn't long term debt it is more appropriate having it as an equalising negative Cash Balance for accuracy.
For more long term credit card debt that you're trying to tackle and pay down - Enter this into the liabilities sheet.
How do I account for Salary Sacrificing?
You should enter as your income the amount of money available to you when you're paid after the salary sacrifice has been taken out. For all intents the Sheet is concerned with what money is usable to you after tax/fees has been taken out. This will ensure the savings rates are calculated correctly against the post-sacrificed income.
How do I account for income from a rental Property?
Income from Rental properties is entered into the Side Income tab. Rename one of the example columns as needed and enter the after-tax rent each month. Rental income can also be accounted for in the Property tab in the Net Rent Profit To Date($) row, which will feed into your overall return values.
Investments
I have years of transactions for my investments, what should I do?
In setting up the Sheet, you have the choice to enter as much data as you'd like depending on the level of detail you would like to see. Here are a couple of pointers:
Only enter in transactions for holdings that you currently own. Old sold assets do not have to be entered.
Entering your data for the first time can be tedious depending on the number of transactions you have, but comes with it's benefits. It will give you full profit/loss breakdown on each parcel, accurate performance benchmarking over time and provide accurate data for the Capital Gains components of the Sheet. In most cases this will involve some longer initial setup, but after this time on a month to month level entry into the Sheet is minimal.
If you have years of data, including DRP purchases, you do not have to enter these all into the Sheet if you don't want to. Instead to save time you can combine all your prior purchases for a holding into a single transaction. This will ensure these investments are correctly incorporated into your net worth, although keep in mind that this will make performance and CGT calculations innacurate.
If you would like like a middle ground option, if you enter in a correct average value price for this single combined transaction this will ensure your Total Gain ($) figures will still be correct
Can I enter international assets into the Sheet and convert their currency automatically?
Yes you can! The v2 version of this Sheet supports live and on the fly currency conversion across a variety of assets. This will price the assets in the foreign currency, and then convert it live to your local currency for performance metrics and inclusion in your net worth. Supported Assets are:
Cash - See here
ETFs - See here
Shares - See here
Managed Funds - See here
Crypto - Automatically converted
Other Assets - See here
Retirement Accounts ported from Managed Funds tab - See here
How do I swap the ticker for an asset to update to a new pricing provider?
If you want to swap the ticker for an asset to swap between a pricing provider (say from Google Finance to MorningStar or FT.com), follow the instructions below:
Find the NEW ID for your asset using these instructions.
Open the investment tab containing the asset (ie. ETF, Stock, Managed Fund)
Click Edit → Find and Replace
Next to the Search option, change this option from All Sheets → This Sheet
Tick the Match entire Cell contents option
In the Find box enter in the OLD ID. In the replace box enter in the NEW ID
Click Replace All
Repeat Steps 3-7 again in the Dividends Tab
This will update the old values in both your watch table and purchase history.
How do I enter my Spaceship/Raiz/Microinvestment Platform Investments?
For Spaceship Voyager: Spaceship Voyager as of v2.10 now has live price support for all 3 fund types (Universe, Origin, Earth). In the Managed Funds tab please enter the following details:
Fund ID - SPACEVOYUNIV
Fund Name (Important) - Spaceship Voyager Universe
Fund ID - SPACEVOYORIGIN
Fund Name (Important) - Spaceship Voyager Origin
Fund ID - SPACEVOYEARTH
Fund Name (Important) - Spaceship Voyager Earth
Purchase History - Enter in your itemised Purchase History, or if you have a large amount of purchases you can enter in a single purchase that you reguarly update the quantity. Please note if you batch into a single purchase this will make any performance/gain figures inaccurate. Please note prices are only updated every 24hrs. For Raiz & Other Investment Platforms: For other Microinvestment Platforms you have 2 options:
Other Assets Tab (Easiest) - This will mean your investments will be recorded in your Net Worth, you will just have to update the balance before recording your month.
Managed Funds Tab - Enter your platform as a Managed Fund and manually update the Live Price as needed (ignore any warnings). Enter in the Purchase History as provided by your platform.
How do I manage DRP or Dividend Reinvestment?
Distributions that are reinvested need to be entered as new units in the ETF/Stocks tab manually just as you would a normal ETF/Stock purchase. The key difference being that the brokerage is now $0, so overwrite what's in the brokerage Column and set this to $0. To determine the price and number of units purchased, you will need to confirm this with Sharesight or your Broker statement to be confirm these amounts.
How do I return my investments to 3-4 decimal places?
By default the sheet will return live prices to 2 decimal places as this is the default returned value from Google Finance and other sheet providers. If you would like your prices returned to 3-4 decimal places, you can adjust the formula used in the Sheet to allow for this by following the below:
In the watch table, go to the 'Live Price' column of the particular asset you would like to make the change for
Search in the column for GoogleFinance(A2,"price")
Replace this with the formula GoogleFinance(A2,"marketcap")/GoogleFinance(A2,"shares")
Update the cell reference A2 to align with the cell corresponding to the row you're editing. This will still be in column A, you just need to change the row number
And that's it! Please note that this is not an officially supported solution as the Market Cap and Share values returned by Google Finance are often delayed and may not reflect the live price. Furthermore, this will only work for assets that are looked up via the Google Finance API.
How do I account for Margin Loan Investments?
Margin loans needs to be broken out into their investment and liability components in the Sheet. Place all your investments into the ETF/Stock tabs as applicable, and then add the loan component into the Liabilities tab. All proceeds such as dividends would still be treated the same as a normal holding, so no need to track them under a separate sheet.
I've sold all my Shares of a particular holding, can I remove them from the Purchase History table?
It is recommend that you do not remove these lines from the Purchase History table as these figures still play into your in/outflows for particular months. Removing these past purchases will influence your Savings rate across months and potentially make certain Sheet statistics incorrect.
I keep running out of Crypto API calls, why is that?
If you are running a Sheet before v2.10.8, it is heavily recommended that you upgrade to the latest version of the Sheet, as the new Crypto price fetching feature has been completely rewritten and reduced CoinMarketCap API calls by up to 95%. Additionally, old archived Sheets may still be checking Crypto prices in the background. It is recommended after updating to a new Sheet that you remove the Crypto API key in the SheetOptions Tab from your old sheets. As of v2.11 this is automated.
I received some Crypto for free, how do I enter that?
Crypto that is received for free for the sake of accuracy in the Sheet needs to be entered as Crypto Staking Income. If you enter it as a purchase as $0, you will see incorrect CGT and performance values, as well as the gain being incorrect for tax purposes with free Crypto usually being treated as income and not CGT (depending on your region). Please follow these instructions on how to enter free Crypto correctly.
How do I manage private equity stocks?
You can enter these in as you would any normal stock purchase. You will need to enter in your own 'Live Price' and update this as needed (ignore any warnings when doing so). Please note when upgrading Sheet versions you will need to manually enter in the Live Price into your new Sheet.
Last updated
