Custom Google Sheets Portfolio trackers
Let’s find out how can we build a Google Sheets portfolio tracker and discuss its pros/cons.
Once we go through these questions, we will provide a summary here:
What is Google Sheets?
“Google Sheets” is a free online spreadsheets solution. Only a Google account, a web browser and an internet connection are needed to create or edit Google Sheets spreadsheets online. Multiple users can edit the same file at the same time, it’s auto-saved and has revisions (edits) history. It looks similar to common MS Excel spreadsheet with rows/columns and supporting menus and buttons to format cells, for example:
How is Google Sheets different from Microsoft Excel?
Google Sheets and Microsoft Excel integration
So, Google Sheets is an online-first tool which is different from Excel, but it allows Excel integration. You can export Google Sheets spreadsheets as Excel files, or use Google sheets to edit Excel files stored in Google Drive.
Google Sheets vs Microsoft Excel
In the context of building a portfolio tracking spreadsheet, a key difference is that Excel and Google Sheets each has different number of supported functions and formulas. Financial data parsing functions, however, are different between Excel and Google Sheets:
- Microsoft Excel uses Stock Data Type integration which is not available in Google Sheets
- Google Sheets uses Google Finance data which you can access using GOOGLEFINANCE function. This is a key DYI portfolio tracker spreadsheet function and it is not available in Microsoft Excel.
The fact that this key GOOGLEFINANCE function needed for portfolio trackers is not available in Microsoft Excel means that you will only be able to see your portfolio tracker in Google Sheets.
Financial data populated by formulas won’t be seen in Microsoft Excel. For all views/edits you will either need a browser with online access or have a Google Sheets app installed if you wish to work offline as well. You could still manually export all the values from spreadsheet edited in Google Sheets as Microsoft Excel, but that will hold only actual values at the time and updates will be missing.
Note: Google Sheets are known to be fairly slow with larger data sets.
How can I create a Google Sheets portfolio tracker?
GOOGLEFINANCE function is the key function that allows you to access Google Finance data, i.e.:
This function could create any custom spreadsheet that fits your needs. Presentation of the data is completely up to you – rows, columns, pie charts, bar graphs, you name it. There are a number of Google Sheets portfolio tracker templates shared online that you could pick up as baseline for you own spreadsheet.
What investments are available to track in Google Sheets?
Google Sheets data is backed by Google Finance. It contains a large set of stocks, ETFs and mutual funds. You can check whether your investments are supported directly in Google Finance web page:
Pros and cons of using custom Google Sheets portfolio tracker
- Near real time updates of financial data from Google Finance
- Fully customisable appearance
- User experience (cell formatting, majority of functions/formulas) is close to well-known Microsoft Excel
- DYI solution, requires some technical knowledge and manual work for creation
- Requires spreadsheet maintenance work as well manual updates for all transactions
- Financial data is limited to Google Finance (no commodities, cryptos, some ETFs and stocks will be missing as they are not on Google’s tracked exchanges)
- Google Sheets limit data that will be available online, and it cannot be auto synced with Microsoft Excel for offline usage
- Google Sheets presents limitations for users of more advanced features of Microsoft Excel
- Slow with large sets of data
- It won’t have more advanced common portfolio tracker features like screening, news, analysis, transaction auto-synchronisations, etc.
Google Sheets portfolio tracker alternatives
Google Sheets portfolio tracker might be an interesting pet project for techies, but it requires a lot of time. If you just want to get the job done, you might be interested in readily available portfolio trackers. We compared the best portfolio trackers in the market based on a number of criteria (pricing, devices, residency, etc.). You can find comparison details in our portfolio tracker comparison blog.
If you are interested to get busy with hands-on custom portfolio tracker building, Google Sheets might be just for you. And as it stands now, Google Sheets is completely free and unlimited. But financial data is backed by Google Finance, so it won’t have some assets like commodities and cryptos. You can check whether Google Finance has data of all your investments in Google Finance site.
If you are looking for off-the-shelf portfolio trackers, all-in-one trackers like TotFin might save you time and effort to aggregate and update your assets valuation. Also it provides number of out-box features like fundamental metrics, news, P/L filtering, daily emphasis, etc. You might also want to check out our portfolio tracker comparison table which contains analysis of best available portfolio trackers with pricing, features, available devices, regions, features, and other details.