Hello everyone,
A few months back I posted links on ShinyThings' thread pertaining to a spreadsheet I have made that can be used to track your investments and show your returns.
Quite a few people requested access for the file, and so I have continued to work on improving it.
Here are some of the key features:
1. Live prices* and currency rates
2. Total/Compounded returns over time
3. Realised/Unrealised profits and losses and total value
4. Track dividend payments
5. Compare your actual asset allocation with your target allocation
6. TER
7. How much of your portfolio is in accumulating vs distributing funds
8. Track cash deposits/funds: works with USD, SGD, GBP and Euros
9. Ability to change your base currency
10. Customise the order your funds appear on screen
11. Conditional formatting colour coding
12. Charts
13. 3 different modes to help you rebalance your portfolio - the spreadsheet will tell you how much to buy or sell of each fund to reach your desired allocations
* for some unknown reason, neither Google nor Yahoo support the SGX, so no live prices for Singapore stocks.
I will provide some video links which show how it works and it should be set up. I will also provide a demo version for viewing purposes. I would be happy for people to post constructive feedback in this thread.
Public Version for Viewing - here
Please have a look and then if you can see anything you would like improving, let me know. Once I have had sufficient feedback, I will publish a final version which you can download and use.
Video Guide 1 - Overview Sheet
Video Guide 2 - Rebalancing Sheet
Video Guide 3 - Dividends Sheet
Video Guide 4 - User Input Data Here Sheet
Video Guide 5 - Stock Summary, Transactions & Cash Register
Video Guide 6 - Bogleheads Returns Sheets
Video Guide 7 - Linking MyPortfolio with Bogleheads Returns
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
I lost what was for me, a very large sum of money in the Zurich Vista scheme. Luckily I discovered index investing via Andrew Hallam and at least I am now in control of my financial decisions. I hope with this spreadsheet I am able to help others, as others have helped me.
Important! The spreadsheet is built upon InvestmentMoat's freely available sheet. On his website he states that he is happy for people to copy and edit it but I would like to reference him here all the same. Secondly, I also incorporated part of a Bogleheads' sheet which calculates returns. This is also freely available and I acknowledge that this part of the sheet is not my own work.
The rest, however, represents literally hundreds of hours of my time.
Known Bugs!
Re-download the sheet to get the latest version or fix it yourself using the instructions below:
FIXED - On the Stock Summary SGD sheet, only the first row is configured for SGD stocks; the rest are linked to the USD list. To fix this yourself, copy cell B2, select all the cells from B2 down the rest of column B, right click, select Paste Special, select Paste Data Validation only. Done.
FIXED - On the Transactions SGD sheet, in column F (fees) the amounts were showing in £ from row 4 downwards. You can fix this yourself by simply amending the formatting for the column.
FIXED - On the Overview sheet, column M (unrealised gains/losses), the numbers suddenly started to show with zillions of decimal places. Don't know why. Have forced the column to round down to 2 decimal places.
A few months back I posted links on ShinyThings' thread pertaining to a spreadsheet I have made that can be used to track your investments and show your returns.
Quite a few people requested access for the file, and so I have continued to work on improving it.
Here are some of the key features:
1. Live prices* and currency rates
2. Total/Compounded returns over time
3. Realised/Unrealised profits and losses and total value
4. Track dividend payments
5. Compare your actual asset allocation with your target allocation
6. TER
7. How much of your portfolio is in accumulating vs distributing funds
8. Track cash deposits/funds: works with USD, SGD, GBP and Euros
9. Ability to change your base currency
10. Customise the order your funds appear on screen
11. Conditional formatting colour coding
12. Charts
13. 3 different modes to help you rebalance your portfolio - the spreadsheet will tell you how much to buy or sell of each fund to reach your desired allocations
* for some unknown reason, neither Google nor Yahoo support the SGX, so no live prices for Singapore stocks.
I will provide some video links which show how it works and it should be set up. I will also provide a demo version for viewing purposes. I would be happy for people to post constructive feedback in this thread.
Public Version for Viewing - here
Please have a look and then if you can see anything you would like improving, let me know. Once I have had sufficient feedback, I will publish a final version which you can download and use.
Video Guide 1 - Overview Sheet
Video Guide 2 - Rebalancing Sheet
Video Guide 3 - Dividends Sheet
Video Guide 4 - User Input Data Here Sheet
Video Guide 5 - Stock Summary, Transactions & Cash Register
Video Guide 6 - Bogleheads Returns Sheets
Video Guide 7 - Linking MyPortfolio with Bogleheads Returns
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
I lost what was for me, a very large sum of money in the Zurich Vista scheme. Luckily I discovered index investing via Andrew Hallam and at least I am now in control of my financial decisions. I hope with this spreadsheet I am able to help others, as others have helped me.
Important! The spreadsheet is built upon InvestmentMoat's freely available sheet. On his website he states that he is happy for people to copy and edit it but I would like to reference him here all the same. Secondly, I also incorporated part of a Bogleheads' sheet which calculates returns. This is also freely available and I acknowledge that this part of the sheet is not my own work.
The rest, however, represents literally hundreds of hours of my time.
Known Bugs!
Re-download the sheet to get the latest version or fix it yourself using the instructions below:
FIXED - On the Stock Summary SGD sheet, only the first row is configured for SGD stocks; the rest are linked to the USD list. To fix this yourself, copy cell B2, select all the cells from B2 down the rest of column B, right click, select Paste Special, select Paste Data Validation only. Done.
FIXED - On the Transactions SGD sheet, in column F (fees) the amounts were showing in £ from row 4 downwards. You can fix this yourself by simply amending the formatting for the column.
FIXED - On the Overview sheet, column M (unrealised gains/losses), the numbers suddenly started to show with zillions of decimal places. Don't know why. Have forced the column to round down to 2 decimal places.
Last edited:
