Instructions if you don’t have access to Office365 for Excel
If you don’t use Office365, you will not have live stock prices through excel. Here are three workarounds.
1. Using Office365 online, which is free
If you don’t have an account, you can signup for free here. Once signed in, click on the green Excel icon, and you can drag the file to the big plus, which says ‘New blank workbook‘ below it.
After that, when you want to use the spreadsheet, login (if you did not set it to remain logged in), click on the green Excel icon, select ‘PIA-Shares-Tracking-Rebalancing-Template.xlsx‘, and the current stock prices should update automatically.
2. Using Google sheets
If you have a google account and want to use it through google docs:
- Go to Google Drive.
- Click on the dropdown MyDrive and select File upload and upload PIA-Shares-Tracking-Rebalancing-Template.xlsx
- Click on the file you uploaded (if it doesn’t show in the file list, there is a notification at the bottum right saying upload complete — click on that).
- Select all of this table, press CTRL-C to copy to clipboard, and then in Google sheets, select cell J3 and press CTR-V to paste it in. And make Column L a little wider to fit the prices.
|=CONCAT(“ASX:”, K3)||VAS||=GOOGLEFINANCE(J3, “price”)|
|=CONCAT(“ASX:”, K4)||VGAD||=GOOGLEFINANCE(J4, “price”)|
|=CONCAT(“ASX:”, K5)||VGS||=GOOGLEFINANCE(J5, “price”)|
|=CONCAT(“ASX:”, K6)||VISM||=GOOGLEFINANCE(J6, “price”)|
|=CONCAT(“ASX:”, K7)||VGE||=GOOGLEFINANCE(J7, “price”)|
- Do the same for this row (select the whole row), select cell J10 and paste it in.
|=CONCAT(“ASX:”, K10)||VAF||=GOOGLEFINANCE(J10, “price”)|
To fix the formatting of the total rows:
- Select Cells J8-S8.
- Select the flll colour icon from the menu (the first icon in the box after the box containing bold and italics), and change it to light grey.
- While still selected, click the next icon after the flll colour icon (Borders icon), select the one with a pencil, and select a dark blue, then click on top border and bottum border icons.
- Follow the above two steps after selecting J12-S12.
3. Auto importing google finance data into excel
This takes about 15 minutes to set up and requires you to have a google account.
Here is a Video link explaining how, or you can follow the instructions below.
To set this up:
- Open up Google Sheets
- Click on New Blank Document.
- Click up the top left and change the name from Untitled spreadsheet to ETF Price Data (so you know what it is).
- As in the example below:
- In column A, enter the ticker.
- In column B, use the CONCAT function to prepend “ASX:” to the ticker.
- In column C, add the prices by Google. E.g.
|VAS||=CONCAT(“ASX:”, A1)||=GOOGLEFINANCE(B1, “price”)|
|VGAD||=CONCAT(“ASX:”, A2)||=GOOGLEFINANCE(B2, “price”)|
|VGS||=CONCAT(“ASX:”, A3)||=GOOGLEFINANCE(B3, “price”)|
|VISM||=CONCAT(“ASX:”, A4)||=GOOGLEFINANCE(B4, “price”)|
|VGE||=CONCAT(“ASX:”, A5)||=GOOGLEFINANCE(B5, “price”)|
- Click File, then Share, then Publish to web.
- Under Embed, select Microsoft Excel (xlsx)
- Click on Published content and settings and ensure this box is checked: Automatically republish when changes are made so that it automatically updates and pushes changes to the share tracking spreadsheet which we will hook this into next.
- Click the green Publish button and hit ‘Ok’ to confirm.
- Copy the link that is shown:
We are done with Google docs, and you can close that, but make a note of the link at the end.
Now let’s go to the spreadsheet PIA-Shares-Tracking-Rebalancing-Template.xlsx.
- Click on menu Data and the submenu Get Data — From Web
- Enter the LINK you saved from your google doc and hit Ok, then Connect.
- The Navigator window will come up. Select Sheet1, which will give you a preview.
- Click Load, which will create a separate worksheet to load the data into.
- The new worksheet (Sheet1) has been created.
- Click on Sheet1 at the bottum of your excel window and drag the worksheet to the end.
- Also double click on Sheet1 and rename it to GoogleData
- Go back to the Shares worksheet and replace the data in columns J, K, and L like this:
- For the first row (e.g. row 3), select those 3 cells (J3, K3, L3) and hit the delete key.
- In J3, leave it blank
- In K3, enter the ticker symbol (e.g. ‘VAS’)
- In L3, enter the following reference to the GoogleData sheet:
=VLOOKUP(K3, GoogleData!$A:$C, 3, FALSE)
- Do the same For each additional row:
- In column J, leave it blank
- In column K, enter the ticker symbol (e.g. VGAD)
- In column L, enter the VLOOKUP reference but change K3 to K4 since you are updating row 4 now:
=VLOOKUP(K4, GoogleData!$A:$C, 3, FALSE)
If excel does not automatically refresh, you may need to click on refresh manually to update prices (menu ‘Data‘ => ‘Refresh All‘).