My Excel IRR Portfolio Summary

Published November 26, 2020

Updated January 28, 2024

~7 mins read

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.

Excel Then vs. Today

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.

What functionalities are there?

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:

  1. Sentimental value šŸ˜€
  2. They're still providing the cheapest unit trust, at least when compared to Bank / Agents model. Although small, unit trust fees will still eat into your long term returns.

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:

  • Tracking of Overall Portfolio performance (IRR/ROI)
  • Tracking of Individual Fund/Stock performance (IRR/ROI)
  • Tracking of Realised / Unrealised portfolio gain/loss
  • Auto-update of currency exchanges rate
  • Auto-update of fund unit prices, supporting:
    • Fundsupermart Malaysia
    • Fundsupermart Singapore
    • Fundsupermart Hong Kong
    • Global Stock Listings (as long as it's listed on Yahoo Finance) - such as KLSE, NYSE, LSE, NASDAQ, SGX, HKEX, .....
  • Auto-capture of portfolio / fund snapshot for analysis / reporting purposes
  • Basic analytics via PivotTable / PivotChart
  • Easy-to-track Portfolio Allocation (e.g. by investment vehicle and country allocation)

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.

Why not Google Sheets?

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.

Do I have to pay?

That's the good news! I am providing this excel template as part of my giving-back to the communityin 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!

How can I get it?

Just a few important notes before we get there:

  • If you're currently using my previous v2.2 stable, you can use the "Polarzbearz's FSM-to-Spreadsheet Conversion Tool" and go to the section for "2.2 to 3.1 FILE CONVERSION". Otherwise, you can re-import and convert latest FSMOne historical data using the same tool.
  • If you're using the Stock Updater feature, get your stock codes from Yahoo Finance, applicable for all markets (NYSE, KLSE & HKEX).
  • If you want to use Currency Updater, there's a few step required to first get yourself an API key via https://app.exchangerate-api.com/, and update "Reference Data" >> cell D5 (API key)
  • This file contains sample data in "Investment Details", "Portfolio Snapshot", "Fund Snapshot", "Fund Snapshot Exclusion", "Fund Factsheet". UPDATE/REMOVE as required before proceeding!!!!
  • No changes should be required for "Currency", "Fund Info", "Fund Allocation", "Portfolio Summary", "Fund Pivot (Profit)", "Fund Pivot (IRR)" as these are all automated updates. Do not touch anything unless you know what you're doing
  • As this is a free tool, no guarantees whatsoever will be provided. Best-effort-basis support will be given to those who needs it, but would be best if you are comfortable navigating around Excel
  • This version is only fully tested on Microsoft Excel 2016 and above (incl. Office 365), on Windows 10 PC. This version unfortunately do not work on Mac yet as I hasn't developed a way for fetchers to work in cross-platform environment so you'll be on your own (there was some support in the past in the Lowyat thread for older version by other contributors)

Download

2.2C Deprecated Version:Ā https://bit.ly/polarzbearzPortfolioSummary2cĀ (this is the previous stable version patched for 2020 FSMOne and NO LONGER MAINTAINED)
3.1E Latest Version (this one):Ā 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!

Patch Notes

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=" & fundName

3. 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


Footnotes

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

Subscribe
Notify of
guest
10 Comments
Inline Feedbacks
View all comments
10
0
Feel free to comment your thoughts!x