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
Microsoft Excel is a paid product which you need to install to your PC or laptop to use it. Excel spreadsheets will be created and edited on your machine and are saved as Excel files. You can save these files to Google Drive where you can use Google Sheets to edit them online.
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:
If your portfolio has commodities and crypto currencies, Google Finance itself won’t contain this financial data. You would need workaround to get it from other sources, i.e., Yahoo Finance using Google App Script. This is possible but will require advanced technical knowledge.
Pros and cons of using custom Google Sheets portfolio tracker
PROS:
- FREE
- 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
CONS:
- 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.
SUMMARY
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.
