ETFs & Shares
The ETF and Shares tabs tracks your past purchases as well as the current live price movements of your assets.
Last updated
The ETF and Shares tabs tracks your past purchases as well as the current live price movements of your assets.
Last updated
The ETF and Shares helps track your investments and show you a variety of insights into their performance. At the top of the tab you have the Watch table, which holds information about your investments (both those you've invested in and also haven't invested in but are following).
The Purchase History table contains a list of all your purchases to date. Based on these exact prices and dates the sheet automatically creates a variety of performance statistics.
To start tracking a new ETF or Share that you own or have an interest in, simply start from left to right and fill out a new row. International holdings in an overseas currency are supported.
Ticker
This refers to the Stock Market ticker code. This needs both the exchange (ie. ASX, NASDAQ) and the holding code (ie. VGS). This results in a ticker of "ASX:VGS". You can find your asset ticker using the instructions here.
Fund Name (ETFs only)
Your nickname for the holding.
Currency
This is the price that the holding is based in. For example if you have a AUD based sheet and own Apple Shares, you would enter "NASDAQ:APPL" as the ticker and USD as the currency. The Live Price will be the live price automatically converted to AUD.
Target Allocation
For those who like to apply allocations to their portfolios to always keep all their holdings in a target balance, this is where you would set that allocation.
MGT Fee (ETFs only)
This is a place to put the management fee for a holding. This is purely cosmetic and to help you have an overview of fees in your funds.
Location (ETFs only)
This is a cosmetic field to help ensure you're regionally diversified. This populates the graphs on the right side of the tab.
Region % (ETFs only)
This is a cosmetic field to help ensure you're regionally diversified. This populates the graphs on the right side of the tab.
Sector
This is a cosmetic field to help ensure you're diversified across various sectors. This populates the graphs on the right side of the tab. This can also be used to isolate funds that are included in retirement balances (see below).
If you are unsure of the Ticker code, please Google the holding and find the market and ticker codes as below or on other various tracking websites:
Every time you buy and sell an ETF or Share, you must enter it into the Purchase Table.
Ticker
This is a ticker matching the holding in the Watch table.
Purchase Date
The date of purchase.
Volume
The amount of units moved. This is a positive number when purchasing, negative number when selling.
Bought Price
This is the price you purchased/sold at.
Brokerage
The brokerage you paid. This should be automatically filled, but feel free to correct this value if needed.
Sold Units (if needed)
This is the column that automatically keeps track of how much of an original purchase you've sold. This should be automatically calculated based on the first-in-first-out (FIFO) principal. If you would prefer a system other than this, feel free to update this cell.
IMPORTANT: All ETFs & Shares that you still own (ie. have a positive balance for) and have a purchase history for must be in the Watch table. This is where the current price is fetched from.
In a recent update, automatic price lookup support was added including with support for assets in a different currency. Please note caveats to this:
The asset must be in the watch table with the currency value correctly set
To avoid using too much Google allowance, only 50 transactions at a time will be looked up. Once a value has been looked up, replace the formula with a hardcoded value to free up slots for other transactions to be looked up.
This process will be performed automatically when you open your sheet each time.
These looked up values will only be an estimate. Ticker prices fluctuate throughout the day, the value returned will only be the average value from the day, and thus will not be accurate.
For more accurate gain and CGT estimates, it's best to put in your exact price if available.
Support is limited to tickers priced via Google Finance
In the v2 version, the sheet has support for including international ETFs and Shares and converting this live to your local currency. These assets are then included in your net worth with the current days exchange rate.
To enter an international asset into the Sheet, it must first go into the Watch table. As a first step, enter the assets ticker (column A) and name (column B). Lastly, enter the currency for the asset based on where it is priced (ie. USD for the NYSE - New York Stock Exchange) into column C. You can confirm this currency by searching for the holding and finding its listing price:
As you can see in the watch table screenshot, the Live Price column will now display the current price in your local base currency. If you take NASDAQ:TSLA as an example in the screenshots, the current price is $643.38 USD, but the Sheet now displays a converted value of £467.96 GBP in the Live Price column.
Entering in the purchase history of an international is much the same as as a local asset. The only distinction is the Order Price column. The order price must be pre-converted upon entering.
For example: You have the UK Sheet (GBP Currency) and purchased 10 Shares of NASDAQ:TSLA for $250USD. You would then enter £181.84 in the Order Price column.
Automatic price lookups - for v2.14 and above:
In a recent update, automatic price lookup support was added including with support for assets in a different currency. Please note caveats to this:
The asset must be in the watch table with the currency value correctly set
To avoid using too much Google allowance, only 50 transactions at a time will be looked up. Once a value has been looked up, replace the formula with a hardcoded value to free up slots for other transactions to be looked up.
This process will be performed automatically when you open your sheet each time.
These looked up values will only be an estimate. Ticker and currency prices fluctuate throughout the day, the value returned will only be the average value from the day, and thus will not be accurate.
For more accurate gain and CGT estimates, it's best to put in your exact price if available.
Support is limited to tickers priced via Google Finance
For purchases made in the past where you need to lookup the exchange rate, you can use these resources:
In a future version of the Sheet support is being added to automatically convert past transactions.
If you would like to expand the watch table, please follow the instructions below:
Step 1 - Right click on a row that contains a yellow holding.
Step 2 - Click Insert 1 above.
Step 3 - A new row should have been created but it will be empty of all formulas. To fill it with the correct formulas, right click on the row you clicked on in Step 1 and click on Copy.
Step 4 - Click on the empty row you created in Step 2, right click and click on paste.
This should expand the table and fill the new row with all the required formulas. Please repeat this as needed.
