I get this question frequently - "How do you track your portfolio? Can you share me the template?" and the truth is - I am more than happy to do so.
Personally, I stumbled upon the amazing wonders of what Excel IRR can do, during my early days of investment (or rather, baby step on venturing beyond fixed deposits :P) when I landed on Fundsupermart Malaysia's thread in the Lowyat Forum.
One of the forumer was kind enough to share their portfolio excel template, which I took it as the base and subsequently took and developed it further over the years.
This, was how it looked like back then. It started as a simple tracker for Unit Trust funds, capturing line of transactions, manual pricing updates to track the current portfolio value, which in return gives us an idea of the actual fund performance through excel's own XIRR formula.
And over years, it morphed into a monster which expanded the coverage to multi-currency support, expanding beyond the typical Unit Trust funds onto worldwide stock market and dividend supports.
Still, I won't consider the above excel some "success story" or "the next Facebook" - but it's good enough, serves my need, and at the same time helps other whom prefers to stick with Excel but wants to minimise the manual updates required to track their portfolio.
As this excel started FROM tracking Fundsupermart Malaysia's fund, despite that I have technically "retired" from unit trust investments (except PRS), I will still try to maintain support for Fundsupermart for two reasons:
In terms of feature - as this is mainly a "beautified" Excel - don't have your hopes too high up but in high level, the Excel supports:
The list will continuously evolve (especially my own "DEV" version) - though for stability (or security) purposes I generally do not release new version unless there are significant changes, so don't be surprised if you see some missing features compared to the version I am using.
For example, my own version have expanded coverage to auto StashAway portfolio fetcher, dividend logs, scheduled job to run updater in background, scheduler for auto-input of new transactions, etc. But these are generally not released as it's still in development stage and typically have unresolved fundamental issues - i.e. how to avoid the need of hard-coding username/password. I generally release major version every 2-3 years, but usually depending on mood.
The next most frequently-asked question that I always get - once I established the fact that I track my portfolio using Excel, is "why not move it to Google Sheets? You can do it online / via mobile phone"
They're not wrong. Google Sheets are way more powerful and capable than they are 5 years ago. But the truth is? I am lazy. I am lazy to re-design the entire piece onto Google Sheet or other applications - especially since it is still working well for me. Ironically, these are the exact thing I hated during my career which I totally hated managing Resistance to Change during IT Project deployments. Oh well, giving myself the taste of my own medicine š
Jokes aside - if one day the Excel / VBA scripting stops working out of the blue and no longer meet my needs, then perhaps I will start venturing out on exploring others' open source solution or recreating entire thing elsewhere. But until that happen - it's here to stay, at least for my own use.
That's the good news! I am providing this excel template as part of my giving-back to the community - in another word, it's FREE!
Unfortunately, in exchange, I won't be able to provide you 1-to-1 support/hands on/training and I am expecting that you will have at least basic knowledge to navigate around Excel. I've tried my best to keep it as simple as possible, added some basic navigation guides (on how to add funds etc.) within the Excel itself.
The Excel contains VBA scripts for its core functions to work (e.g. Price Fetcher, Portfolio Snapshot), and are completely open source. So if you have programming / IT background, feel free to take it further and develop/customise your own version by forking a new branch - as long as you attribute the credits back to me whenever applicable.
If you'd like - you can also buy me a coffee / tip any amount you'd like below!
Just a few important notes before we get there:
3.1E Latest Version:Ā https://bit.ly/polarzbearzPortfolioSummaryv31d
FSM Conversion Tool (only compatible with v3.1):Ā https://bit.ly/polarzbearzPortfolioSummaryConversionToolv31c
If you have enjoyed my works and would like to support me, feel free to use any of my referral links here or buy me a coffee here!
v3.1e [28 Jan 2024]: Minor fix to replace deprecated YAHOO API. For advanced users, follow steps below:
1. Go to "Developer" > "View Code" > look for "PROD" under "Modules" folder
2. Search for this line:
XML.Open "GET", "https://query2.finance.yahoo.com/v7/finance/quote?symbols=" & fundName3. Replace with this line:
XML.Open "GET", "https://query1.finance.yahoo.com/v8/finance/chart/" & fundName & "?region=US&lang=en-US&includePrePost=false&interval=1d&useYfid=true&range=1d&corsDomain=finance.yahoo.com&.tsrc=finance"PREVIOUS 27/5/2023 UPDATES: Yahoo Finance has decided to terminate it's API service and currently returns an error message below when fetching price via its API, hence part of the auto update feature will stop working until Yahoo Finance restores its API service or an alternative is found.
{"finance":{"result":null,"error":{"code":"Unauthorized","description":"Invalid Crumb. For Developers - https://bit.ly/yahoo-finance-api-feedback"}}}Their full responses below.
Yahoo Finance | API Feedback
Weāre sorry for the inconvenience, but API-level access to Yahoo Finance quotes data has been disabled.Yahoo Finance licenses data from 3rd-party providers that do not currently authorize us to redistribute these data in API form. Licenses that authorize redistribution come with a greater cost that varies depending on a number of factors, including whether the data is for personal or commercial use, the type of data, the volume of queries, and additional features which may be available.
We would appreciate your feedback to ensure that we can continue to serve your needs. By understanding your intended use of these API data, we will be better able to acquire the appropriate licenses. We appreciate your feedback, and we read every response.
https://bit.ly/yahoo-finance-api-feedback
v3.1d [26 Jan 2023]: Added support to track IBKR Currency Conversion fees (a.k.a the infamous $2USD per conversion) as well as FOREX gain/loss when sending monies overseas.
v3.1c [1 Nov 2020]: Hotfix for FSM Malaysia price fetcher since their new layout basically broke the script.
Read more here
v3.1b [23 Oct 2020]: Sneaked in a small change to add additional major stock exchanges (NASDAQ, SGX, LSE) and also added one generic āOthersā for other worldwide stock exchanges. As long as it is listed in Yahoo Finance, you can fetch the price.
Read more here
If you have been using my Excel for a long time - I thank you for your support and hope that it have helped you in your journey towards financial freedom.
If you're new and are looking for some way to keep track of your multitude of portfolios - I hope this Excel would be useful for you. If not, there are plenty of other solutions out there and just don't stop finding - you will surely be able to find something that fits your needs.
Cheers,
Gracie
wpDiscuz