After my previous post reviewing my progress in 2019, one reader wanted to know how I track my investments.
Just one question, how do you create a sheet that automatically track your portfolio such that you can plot graphs like this?
I think your XIRR chart and what not is really cool too. Would you mind sharing the spreadsheet so I can take a look at how you did it up?
HY
So I figured that some of you guys might feel the same!
Now, the spreadsheet I use on a daily basis was not really created to be shared and is therefore a bit of a mess (ok… a lot of a mess) but since I figured that you guys might find it useful, I cleaned it up and created a template that should be easier to use and reference.
You can check it out here:
FIRE-Path Lion Portfolio Tracking Google Sheets Template
Since I didn’t want anybody but me to mess up the template, I’ve set it to read-only for those with the link. In order for you to make use of the spreadsheet yourself you must create a copy of the sheet by going to “File > Make a copy.” Now you have free rein to edit the sheet in any way you need for your own personal use!
How to use the sheet
Even though I tried my best to ensure that the spreadsheet is easy – or easier – to use, I think some explanation will be helpful. So if you want to understand how to use each sheet in detail, read on!
I’ll be explaining the template by breaking down each sheet in the template one by one. If you take a look at the bottom of the screen, you’ll see that there are a total of 6 sheets:
Let’s start first with the Summary sheet.
Summary
The “Summary” sheet contains the current snapshot of your portfolio as of today.
You generally do not need to make any changes to this sheet unless you are adding new tickers or cash to the portfolio.
The information in this sheet is at both Portfolio and Ticker level. Let’s take a look at the portfolio level information first.
Portfolio Level Information
At the portfolio level, you get to see:
- Total Portfolio Value: How much the portfolio is worth today by adding the current value of all the tickers and cash together.
- Total Cost of the Portfolio: How much money was used to purchase the current portfolio.
- Total Gain/Loss Amount & Percentage: Portfolio Value minus the Cost of the Portfolio.
- Current XIRR of the Portfolio: Annual rate of return of the portfolio based on the trades in the “Trades” sheet.
- Total loss due to Commission & Fees: The total fee and commission (from the “Trades” sheet) compared to the total portfolio cost.
- Total loss due to Exchange Rate charges: The total difference between the rate charged by the brokerage (documented in the “Trades” sheet) compared to Google Finance rate – which is the mid market rate.
- How much yearly and monthly income the portfolio value translates to (Based on the Safe Withdraw Rate in the “Projection” sheet.)
Pretty useful if you’re a data geek like myself.
That’s not all though, there’s also details at individual ticker level.
Ticker Level Information
At the ticker level, it shows you all of the stock tickers that you hold currently along with the number of shares of each.
On top of that, there are a few important variables that are calculated for you for each ticker:
- Current price of the ticker from Google Finance (and STI ETF Price from Bloomberg)
- Total value in SGD using today’s Google Finance exchange rate
- Percentage Allocation of the Portfolio
- The Dollar Cost Average price in the original currency of the ticker (DCA Price OCY)
- The Dollar Cost Average price in SGD
- The total cost in the original currency (Total Cost Basis OCY)
- The total cost in SGD (Total Cost Basis SGD)
- Your current rate of return for that ticker (XIRR)
Nifty! Best of all, all of this information is calculated based on the “Trades” sheet (which we’ll touch on next) so you don’t have to touch this sheet much at all! Well except when you need to …
Adding New Tickers
If you’d like to track other tickers that’s not already on the sheet, you will need to add a new row and copy over the formulas from the other row depending on what currency the new ticker is.
You will need to ensure:
- The currency is correctly selected
- The conversion rate formula is correct
- The portfolio value and cost calculations includes the new ticker
Adding Cash
If you are adding cash to the portfolio without using it to purchase shares right away or if you’ve sold some shares and are then accounting for the cash being added to the portfolio, you’ll need to update the “QTY” column for the SGD (or if you need USD, you’ll need to add that row as well.)
Trades Sheet
This sheet is where all the magic happens. This is where all your trades are documented and thus it’s the most important sheet to get right. The green columns are where you are meant to make changes & input information.
Let’s break it down in detail so you know how to fill this out.
For each of the trades, you are entering a few things:
- Date: The date that you made the trade. You should be entering this at the exact same time as the trade gets executed to ensure that tracking is accurate.
- Ticker: The ticker of the asset you are purchasing. This ticker should match the ticker in Google Finance as well as the “Summary” sheet so that all the calculations can be automated.
- Price: The exact price of the ticker when you made the trade (in the ticker’s currency.)
- Currency: The original currency of the price.
- Rate: The exchange rate charged by your brokerage to exchange SGD to the currency used for the trade. This is used to calculate your exchange rate loss. This number should be provided by your broker.
- SGD Equivalent: This is the price in SGD (Price x Rate.)
- QTY: The quantity of shares that was purchased or sold. If it’s a BUY trade, then the QTY should be positive. If it’s a SELL trade, then the QTY should be negative.
- Total OCY: This is the total cost of the trade (excluding brokerage fee) in the original currency (Price x QTY).
- Total SGD: This is the total cost of the trade (excluding brokerage fee) in SGD (Price x Rate x QTY)
- Commission: This is the commission charged by the brokerage. Sometimes you might have to figure this out by deducting the total cost of the trade with either Total OCY or Total SGD to find out the brokerage fee.
- Dividend (SGD): If the ticker sometimes pay dividends, you can enter the dividend here. Example in row 40 for just accounting for the dividend. If your platform reinvests dividend for you, you can put it into the same row as the trade (i.e. Row 4)
- DPS: This is dividend per share (not Damage Per Second for you gamers out there). You can calculate this by getting the total dividend payout and dividing it by the total shares you hold.
- Cashflow: This is the total value of the trade. Usually equal to Total SGD + Commission.
- XE Rate: This is only relevant for any trades in foreign currency. It is the exchange rate from Google Finance at the time of the trade. This allows us to calculate the difference between the Google Finance rate (mid market rate) against the rate charged by the brokerage (the “Rate” column.)
- Rate Diff: (XE Rate – Rate) divided by the XE Rate.
- Rate Loss: Total amount rate loss of the trade in SGD.
- Commission %: The percentage of Commission to the Cashflow of the trade.
The last row in this sheet should contain Today’s Date and the Total Value of the portfolio. Always enter your trades above this row as this row is used in the daily XIRR calculation by assuming that today’s XIRR rate is based on you selling your entire portfolio today.
That’s it! If you have questions regarding this sheet do post your comments below and I’ll get back to you!
Charting Sheet
The coolest sheet in the template. This is where all the beautiful graphs gets drawn.
In this sheet, you’ll see 4 different chart but you can always add more information on your own.
The existing charts are:
| Chart | Description |
| Portfolio Value and Cost Over Time | Allows you to visualise how much your portfolio is worth vs how much you’ve put into the portfolio. |
| Total Return and Annual Return (XIRR) Over Time | Allows you to see the total return against the annual return over time. I’ve also included the daily average lines for each to see the longer term trend rather than focusing on just the daily values. |
The subsequent 2 charts are just splitting out the gains and charting them out over time in their own chart so it’s easier to see.
This sheet gets updated automatically everyday, you don’t have to do anything except setting it up the first time.
In order to set this sheet up for yourself, you’ll need to clear every row except the last row.
Every column in the last row in the sheet contains a formula that inputs the portfolio snapshot for the current day.
Every night, a script will run that creates a new row with the formula and freezes the current day’s information in place so that the graph grows automatically each day.
You can view the script that will be run by going to “Tools > Script editor”. I wouldn’t touch the script in here unless you know what you are doing, but essentially the function “UpdatePortfolioValue()” copies the last row of the “Charting” sheet and pastes it into a new row, then goes to the previous row and freezes the values inside. This essentially takes a snapshot of your portfolio each day.
I have scheduled this function to run daily before midnight before the date changes to lock in the portfolio state.
If you want to track additional items daily, all you have to do is add a new column to the “Charting” sheet so that it gets copied with everything else on a daily basis.
NonGFinanceTickers Sheet
The last interesting sheet, but necessary. Some time in 2018 Google Finance no longer returns the price of some tickers, usually Singapore listed stocks – and that includes the STI ETF – when it still does for the other stock tickers.
Therefore, updating the STI ETF price is not as easy as the other stock tickers, so I created a workaround.
The workaround involves pulling the price from Bloomberg’s website. Basically find the URL of the stock that you want to track and put the link into the formula. For example the URL for SPDR Straits Times Index ETF (or ES3.SI) is: https://www.bloomberg.com/quote/STTF:SP so use this formula:
=IMPORTXML("https://www.bloomberg.com/quote/STTF:SP","//span[@class='priceText__1853e8a5']")If you want the quote for Nikko AM Singapore STI ETF then the URL is: https://www.bloomberg.com/quote/DBSSTI:SP and you’ll need this formula:
=IMPORTXML("https://www.bloomberg.com/quote/DBSSTI:SP","//span[@class='priceText__1853e8a5']")[Update 26-Sep-2020] As of 16-Sep-2020 the above Bloomberg website no longer works and will return N/A if we use the formula (as pointed out by many commenters below.) After looking around I couldn’t find an alternative solution, however one commenter – Bryan (Thank you for your hard work!) – managed to find another workaround that is still working. I’ve added his formula into the template and below. Basically we’re using the CNBC website now instead of Bloomberg. However, this could also stop working any time once CNBC realises a lot of people are using their site to automatically update spreadsheets… so we might run into this issue again.
=IMPORTXML("https://www.cnbc.com/quotes/?symbol=STTF-SG","//*[@id='cnbc-contents']/div/div[3]/div[3]/div[1]/div[2]/div/div[1]/div/table/tbody/tr[5]/td[1]/span[1]")I think this method should be usable for other stocks as well if you cannot find it on Google Finance. The only drawback here is that if Bloomberg changes the CSS or the webpage structure of their website, then this formula may not work anymore – but it’s the easiest way to get stock price for now. My personal method is more complex and is much harder for others to use.
This sheet’s primary purpose is to store the updated price of STI ETF which can then be referenced from the “Summary” sheet.
Projection Sheet
This is a fun sheet for me to do some projections on when I will reach FIRE. I highlighted the fields that you can play with in Green.
The sheet will simulate, based on the interest rate provided, how long it will take for you to reach your FIRE amount.
Here are the variables that you can adjust and what they mean:
- Rate of Return: The growth rate of the stock market (after accounting for inflation.) I assume 8% annual growth.
- Saving Amount: Your saving amount per month at the start of the simulation.
- Yearly Increment: The amount your monthly saving will increase on a yearly basis.
- Bonus: The amount of bonus you will be investing each year. I assume that bonus is paid once every 12 months and is calculated in multiples of your monthly salary.
- Starting Portfolio Amount: The amount you already have in your portfolio at the start of the simulation.
- Target Income (Monthly): The monthly passive income you need after you reach FIRE.
- Safe Withdrawal Rate: The percentage of your portfolio you are targeting to withdraw each year. The recommended amount is a number below 4%. I am currently targeting 3.33% to be conservative.
That’s it! Once you have all this keyed in, it will calculate the portfolio value you’ll need and determine roughly how long it will take for you to save the amount based on the rate of return that you entered.
It will also take your current portfolio amount to tell you that based on your current portfolio, which year you are roughly at within the simulation and how many years is left on your FIRE journey.
This is just a fun sheet for me to look at once in a while. I usually like to see that I’m ahead by looking at the “Current Year” value. If I have only started investing for 2 years but “Current Year” is saying a number larger than that, it means that I’ve saved more quickly than the simulation expected.
This is a very simple – single rate of return – simulation, so don’t take this as anything more than “just for fun.”
Now, to the final sheet.
Yearly XIRR
This sheet is the most recent sheet that I’ve added to the template. It’s meant to help us benchmark our investment portfolio against other investments or investment funds.
Normally when we look at purchasing Unit Trusts or Mutual Funds, they will quote their return by year. For example the discrete yearly returns in the case of IWDA on iShares website are:
- 2015: -0.77%
- 2016: 7.73%
- 2017: 22.45%
- 2018: -8.65%
- 2019: 27.76%
And so on.
However, our portfolio XIRR in the “Summary” sheet shows the rate of return from the start of our investment journey until today, which could include multiple years taken together. This cannot be used to compare with the yearly return that is quoted on many financial instruments or other investment portfolio.
This is why I created this sheet.
The sheet assumes that at the start of each year, we decided to buy our entire portfolio at cost or the value of the portfolio, whichever is higher.
Then as the year goes on, we continue to invest using the trades in the “Trades” sheet.
Finally at the end of the year at 31-Dec, we liquidate the entire portfolio and calculate the return for the year.
This will give us the total rate of return for that particular year which can then be used to compare ourselves with investment funds, index performance, or other people (main reason!)
Note though that the number is really useful if you have been investing for the entire year, so if you started investing in the middle of 2016, then your 2016 number isn’t really going to be that useful for comparison. I hope that makes sense.
Adding a new year
In order to add a new year, copy the Date and Cashflow columns and change the dates to the new year’s date.
Then add the new year to the left and copy over the XIRR formula while making sure to change the columns in the formula to point to the new columns you’ve just added.
That’s it!
Conclusion
There you have it – the spreadsheet I’ve been using regularly to track my portfolio.
I hope that you’ll find it useful.
Let me know if you think I could add or improve the sheet and I’ll see if I can add it in!
Until next time!
FPL
Hey FPL!
Have you considered using stocks cafe? Up until a few months ago, I also tracked my portfolio using an excel spreadsheet. My set up was not as comprehensive as yours but even then I found it a hassle to update it for every new transaction/dividend 😅
Stocks cafe helps me do that automatically now and I love that I don’t have to worry if my formulas have any typos lol. Do check it out!
Here’s my referral link which gives you a Friend status for 2 months: https://stocks.cafe/user/tosignup?referral_code=joceyng
Hey Joce, thanks for the suggestion! Stocks Cafe does look pretty powerful but unfortunately it doesn’t support stocks on London Stock Exchange yet (which are my largest holdings.) I also really like tinkering with spreadsheets (I know, super geeky) so I guess I’ll have to stick with my spreadsheet for now! 😀
It actually support tickers from LSE.
Hello. For the Trades SpreadSheet, is the commission in USD or SGD? thanks
Commission is currently stored as SGD 🙂
Hi ,Thank you for the beautiful spreadsheet!
I have adjusted the spreadsheet to have a portfolio of ES3, MBH and IWDA. However, I am not able to get the charting tab working where it only show today’s values instead of charting before midnight every day. I followed the instruction of “In order to set this sheet up for yourself, you’ll need to clear every row except the last row.”
Here are some changes that i made personally:
1.Adding a new ticker for MBH
2. Using Yahoo Finance to track the price for ES3 and MBH instead of the one you used, Below are what I inputed in the summary tab for their current price:
=ImportXML(“https://sg.finance.yahoo.com/quote/”&A5&”/history?p=”&A5, “//tbody/tr[1]/td[6]”)
=ImportXML(“https://sg.finance.yahoo.com/quote/”&A4&”/history?p=”&A4, “//tbody/tr[1]/td[6]”)
May I know how do i resolve this so that I can do the proper charting for everyday? 🙂
(Have already tried for 3 days but could not find a solution >< )
Hey Damien! Glad you like the spreadsheet! I’ll try to help you through this, would you mind sharing your sheet? If you prefer not the share then I’ll need to understand a bit more about the issues you’re facing.
One thing to check, do you have the “UpdatePortfolioValue()” function in “Tools > Script editor” scheduled to run daily? This will copy the last row in the Charting sheet and paste it in a new row and then past the value back into the previous row so you get the daily snapshot of your portfolio on a daily basis.
If that is already scheduled then I’ll need to understand what behaviour you’re seeing 🙂
How do you get the functions to run daily?
I’m a complete newbie at this so forgive me for asking what may seem to be obvious!
No problem! You can get the function to run daily by clicking on the “Clock” button between the “Save” and “Play” button in the Script Editor screen. You’ll have to authorize the script to run on your sheet and then you can set up a trigger that runs daily at a certain time and execute the function. Let me know if you can find it.
Thanks! It should be running fine now.
I’ve got another question though if you don’t mind – I started DCA investing a couple of years ago, but haven’t been really keeping track of my investments and payouts. I thought I should do it properly at the start of this year as part of my NY resolution (lol). I now have a couple of holdings in STI, SBS bonds, Philips APAC Div Reit, while the majority of my cash is in a Citi Priority account earning interest.
So my questions are:
(i) Would you recommend that I withdraw most of my cash in the Citi account to invest in the international component of the portfolio, or just leave it there to earn interest and going forward invest in the international component until it matches the allocation I want?
(ii) How should I use your tracksheet to record pre-existing investments? My understanding is that your tracksheet is premised on recording each and every trade, but it’s difficult for me to do that now.
Thanks in advance – your blog is really helpful for newbies like me!
Hey Nic, great to hear the sheet is working for you now! It can be a bit complex to set up the first time, but glad you’ve got it running.
For (i) this really depends on your risk appetite as well as your individual situation right now. Only you will be able to tell whether you are OK to risk the cash, remember investments is not a guarantee! Select an allocation that you are comfortable with, have some cash cushion for emergency and then build your portfolio towards that allocation. 🙂
( ii ) Yes the sheet is ideal if you’re starting to track from scratch. You have two choices, 1. don’t worry about the past, just track from today onwards. 2. If you’d like to track from the past, you’ll need to manually get the share price history of all your tickers and work backwards using your trading date. It’s possible (that’s what I did when I first set this sheet up 1 year after I started, but it’s very tedious.)
Hope that makes sense! I’m glad you found my blog useful! Do let me know if you have any further questions.
Hi, thank you for helping me with this! I have made a copy of the spreadsheet and attached it below:
https://docs.google.com/spreadsheets/d/1-_8DcKRh172QuWrQh_gZzXNbgdf4CirnGRYvFON92ro/edit?usp=sharing
Here are the problems that I am facing:
1. The XIRR column in the Summary tab only shows “#VALUE!”
2. Unable to set the daily charting where it only shows today’s value
3. For the projection tab, I am not able to get an accurate value for the FIRE year as I think the formula for “FIRE Year” is limited to less than 15 years only?
4. Unable to set the Yearly XIRR tab (Only record today’s value as well)
Here are the changes I made for your reference:
-Using Yahoo finance to track the current price for ES3 and MBH in the Summary Tab
-Adding a new ticker for MBH
-Removal of the ES3 Tab and the code to track ES3 in the code editor
Really appreciate your help for this because I feel that your spreadsheet is quite good and will be able to document my investing journey as well 🙂
Hey! I requested Edit access to your sheet so I can help take a look.
Thanks so much for helping me in getting my own spreadsheet set up! You are a really great person for taking time out and correcting all the errors i have encountered. Hope that you will be filled with happiness daily and reach your desired goal soon!
Cheers 🙂
Happy to help! 😀 Good luck on your journey too! You’ve got a great start!
Hi, its been some time and the market is really going through uncertainties. Both IWDA and ES3 has been down consistently and my profits for the past whole year have now turned into losses.
Even though I know that we should all believe in the long term result, I still cant help but feel uncertain and worried. This is because I just received another lump sum to invest in and afraid that if i invest in IWDA now, it will continue to plunge. Do you have any advice on how to deal with this and your take on the current market outlook? 🙂
Hi Damien! Thank you for asking your question! Extremely timely and I am just writing up a post on this right now. It should be live shortly and I will let you know once it is 🙂 Stay tuned!
I’ve just published the post! You can read my thoughts on this here: https://www.firepathlion.com/covid-19-how-im-investing-through-this-uncertainty/
Hello, just wondering if a particular ticker (eg G3B Nikko Am STI ETF) is not in google finance, is there any other way to track it?
Hi ZX! Fortunately G3B is available through Bloomberg and you can pull the price using this Google Sheets formula:
=IMPORTXML(“https://www.bloomberg.com/quote/DBSSTI:SP”,”//span[@class=’priceText__1853e8a5′]”)
Let me know if that works!
Hi, thanks so much for spending so much of your time on this ‘labour of love’ especially re pulling in daily prices from Bloomberg or other external source. !
Could you please let me know if just inputting this formula below:
=IMPORTXML(“https://www.bloomberg.com/quote/DBSSTI:SP”,”//span[@class=’priceText__1853e8a5′]”)
in a cell is enough to automatically get daily prices or do I have to do the script editor thing ?
(senior newbie fumbling thru spreadsheets – more familiar with Lotus 1-2-3 !)
Thanks
Sam
Hi Sami! Glad you are finding this content useful! Putting in that formula will grab the prices daily into that cell, but will not help you in tracking and capturing the prices daily for the charts – you will still need to use the script for that so your graphs are updated with new row of information each day.
Hi!
I’m new to this, and am happy to find your spreadsheet as I think that it’s not too complicating for my 3 fund portfolio. However, I’m not very sure how to set about adding a ticket for MBH. Could you help me with that? I’m not very good at excel. 😀
https://docs.google.com/spreadsheets/d/1jzdnNJebkCgSm0M1iZ86tpJoHaMgx9BLviPOQuLBh7I/edit?usp=sharing
Hello Firepath lion!
Many thanks for sharing your spreadsheet – it was very insightful for me!
I don’t usually leave comments, but I saw a few questions about adding other counters (especially those in SGX)
Here’s how I did it for my case, and readers who are interested to do this, feel free to follow the steps below.
[Disclaimer: I found it a challenge to get the live price of the stock, so my set up is to get the closing price for that counter from the previous day. Furthermore, as this sheet is for long term tracking, I probably should not be so concerned about the live price. ]
Step 1: Create the spreadsheet which you want to paste the data in, in my example below: Sheettopastevalues
Step 2: Go to script editor in Toolbar –> Tools –> Script editor
Step 3: Copy and paste the code below wholesale into the Script editor and click ‘save’
Step 4: Code to copy whole sale
function getAllData() {
/*Clear previous day’s data*/
var range = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Sheettopastevalues”).getRange(‘$A$1’);
var sheetName = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Sheettopastevalues”);
var queryString = Math.random();
range.clear();
/*Call function to get previous day’s closing price*/
getMBHData(sheetName, queryString);
}
function getMBHData(sheetName, queryString) {
var cellFunction = ‘=IMPORTHTML(“https://sg.finance.yahoo.com/quote/MBH.SI/”,”table”,1)’;
sheetName.getRange(‘$A$10’).setValue(cellFunction);
}
Step 4: In the script editor, click the ‘clock looking icon’ and set the trigger for Daily, 10-11am
(Based on my checks, Yahoo finance seems to get updated about 9-10am the following day, so this setting the trigger 10am onwards would be most ideal)
Step 5: Reference the cell with the previous day closing price in this case, Sheettopastevalues!A2
…. and that’s it!
To run Updateportfolio value in the same code, just include this line as the last line in the code, so that all the functions can run in one shot
UpdatePortfolioValue();
Hope this helps!
KT
Hi KT!
That’s amazingly detailed and I’m sure this will help others with their spreadsheet! Thank you for contributing this for the community!
I would agree that the live price is more of a “cool to have” but not necessary part of the spreadsheet, given that we are long-term buy and hold investors, you are completely correct that last day close price will do perfectly well in this scenario.
I do have a small question though. What is the purpose of the queryString that you pass along to “getMBHData” that doesn’t seem to get used (it’s currently just a Math.random() value.)
Thanks again!
FPL
Hello FPL!
Pleasure : ) [queryString response all the way at the bottom]
One more thing about the code above: I only had it created this week, thus I’ve not had that much an extensive amount of time to test just how accurate / foolproof it would be.
The main drawback is the 100% reliance on the data piped in from Yahoo. One thing I have noticed is that the table I ‘pick up” from Yahoo does not seem to be the most updated some times (consequently, making our data reference not accurate). Maybe this is where referencing a live price would have an advantage. [If any other readers have recommendations to improve this, please feel free to share with me! I’m mainly trying things out, so always good to learn from how others would do it. It could be I am making things more complicated than it should 😉 ]
Another workaround I have thought of now is to directly reference the yahoo finance page with the historical data, in this case:
https://sg.finance.yahoo.com/quote/MBH.SI/history?p=MBH.SI
Now the code to copy and paste whole sale would be:
function getAllDatav2() {
/*Clear previous day’s data*/
var range = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Sheettopastevalues”).getRange(‘$A$1’);
var sheetName = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Sheettopastevalues”);
var queryString = Math.random();
range.clear();
/*Call function containing the counter you want to run.
Feel free to create more functions*/
getMBHData(sheetName, queryString);
/* Uncomment to run this
UpdatePortfolioValue(); */
}
function getMBHData(sheetName, queryString) {
var cellFunction = ‘=IMPORTHTML(“https://sg.finance.yahoo.com/quote/MBH.SI/history?p=MBH.SI/”&”queryString”,”table”,1)’;
sheetName.getRange(‘$A$1’).setValue(cellFunction);
}
//But now to reference the previous day data, I would use a vlookup (instead of just a straightforward cell reference)
So in the cell to paste the value, we’ll type, so that we get the previous day’s closing price:
=vlookup((today()-1),Sheettopastevalues!$A:$E,5,false)
Okay, again this workaround seems to vary from counter to counter – the data in Yahoo finance does not seem the most consistent. For example here: https://sg.finance.yahoo.com/quote/SWRD.L/history?p=SWRD.L – the historical data for SWRD seems to be missing, so in this case I would use the table from the main page and not the historical data one.
Back to the queryString question!
You’re right! I totally missed that out, it’s meant to make each run unique and prevent pulling cached data, so that the figure would be the latest one! [Though I got this idea from searching on the internet, so hopefully my explanation is as intended 😛 ]
=IMPORTHTML(“https://sg.finance.yahoo.com/quote/MBH.SI/history?p=MBH.SI/”&”queryString”,”table”,1)’;
-KT
Apologies. Newbie to excel & charts. Can I save the chart to excel resident in thumbdrive & get the chart updated as well? Or have I to save it to google docs?
Thank you for your advice.
Hey Mike! No problem! You’ll need the sheet to stay on Google Docs for it to keep updating 🙂 Won’t work from thumb drive.
Hi FPL,
Thanks for sharing this file. Just wanted to check, I tried to make a copy and most of the things are working fine, just the charting sheet doesn’t seem to freeze the current day row and copy the formula to a new role. Before this, I tried to delete/clear row or make a few copies of the sheet, or manually click run the script but it still doesn’t automatically run the script every night.. Can you please advise? Thanks.
Hi N! In order to get the script to run automatically every night, you’ll have to open the Script Editor under the “Tools” menu. Once you have that open, you can click on the clock icon next to the Save Script button above the code and create a scheduler to run the function based on the time of day. This Quora post might be of help: https://www.quora.com/How-can-I-periodically-run-a-Google-Script-on-a-Spreadsheet
Hi lion!,
I really appreciate this tool you have provided us with and is a big help. However I have encountered some issues I need your help with
The XIRR of my sheet is constantly returning errors on the Summary page, Charting and Yearly XIRR Page. Would really appreciate the help!
Link to copy: https://docs.google.com/spreadsheets/d/1LP7Vl_uHkWyHHUM-sbnvxqDovvWeQkXSDutL5dUV0lM/edit?usp=sharing
-don
Hey Don! Thanks for reading and I’m glad you’re trying to use the spreadsheet! I think I spot the issues that are causing your problems, I’ve requested for edit access, so if you give me edit capability, I’ll try to fix that for you.
I have gave you access! Thanks again!
I’ve updated the sheet. Should be working now.
Thank you so Much! Could you enlighten me on what the problem was? thanks!
-don
Hey! I just made sure that the last row in the trades sheet contains the current value of your portfolio minus your cash value so that it works with XIRR formula. This simulates what returns you’ll get if you sold all of your shares today, which is what we want. Every time you add a trade, add a new row between the last trade and the current final row so the last row always contain today’s date as well as your total value of your portfolio minus cash value.
As for the individual XIRR for each stock, I changed the row to the row of the last trade (one row above the last row) and that should work. Every time you add a new trade, you’ll have to adjust the row number. It’s a pain in the butt, but I haven’t found a better way to do this yet.
Hey Lion Sorry for asking you another question again. If I wanted to add Robo advisor into my sheet, How would I go about doing that? I have been thinking about this and tried various ways to see how it would work but it doesnt seem to work out for me. I DCA into Stashaway every month so adding each individual ticker every month stashaway buy based on their weight allocation does not seem very efficient. Do you have an idea in mind? Thanks!
-don
Hey Don! No problem, unfortunately tracking your investments that’s been made with Robo advisors is not possible as their allocation and holdings are not accessible via Google Sheets. That’s one of the reasons I prefer to do it myself as it gives me a better tracking of my holdings. Robo Advisors do provide pretty good tracking on their own app and website, but that’s not very good when you also invest outside and want to see your overall holding across everything. Right now the only way to do this is to manually update the value of your holdings manually in the spreadsheet if you want to track everything. You could basically add a new row in the Summary sheet just for StashAway and update the value manually daily, but it’s extremely tedious and if you miss one day, it’s going to mean your data isn’t quite accurate.
Hi Lion,
For the purchase of IWDA, how should I input the rate used if i have 2 parts of the transactions whereby i convert SGD to USD and then use the USD for the purchase? The commission amount should be the combination of fees charged for these 2 transactions, right? Somehow with the figures i key in, the total Cashflow amount doesn’t seem to tie. Would appreciate if you can help clarify here. TIA!!
Hey EO! Hmmmm, I haven’t done this myself, however if I were to do this I would combine it into 1 transaction and account for the trading commission and the exchange rate as 2 separate cost.
Is your trade commission charged in USD or SGD? Also when you convert SGD to USD then made a purchase, did you have USD left over or did you end up using all of it exactly?
The purchase of IWDA is charged in USD and cash conversion is in SGD. However, i can have both numbers in SGD as that number is readily available from the statement. Yes, i do have some USD left (not alot) in the account since i am planning to DCA monthly moving forward. But i will try to convert what i estimate i will need and not leave too much USD sitting in the account.
Hi FPL,
Firstly, big thanks and kudos for a great portfolio tracking spreadsheet! I could get most of the cells running as they should, however there seems to be some issues with the Charting sheet. Can’t seem to get the script to “screenshot” the previous day’s values and plot it on the graph.
Need your kind assistance to troubleshoot the script:
https://docs.google.com/spreadsheets/d/1pDDDpOtG9XzX-Ufye1kuiRd8BvyC0UcLt9IWLefyLr0/edit?usp=sharing
Thanks!
-CY
hi IMPORTXML(“https://www.bloomberg.com/quote/DBSSTI:SP”,”//span[@class=’priceText__1853e8a5′]”) works for google sheet
is there a solution for excel? Have tried googling with no success
Hi FPL,
Thanks for sharing your Google Sheet. Now I can keep track of my holdings held in various trading platforms. I ran into some issues and like to seek your help:
1. What does Cash in Summary changes in your sheet?
2. my XIRR is showing #NUM!. would you help to fix this?
https://docs.google.com/spreadsheets/d/1r5YtiTtleuTei774v2GbNLZsd5X8VHcVV6_bQHb60jY/edit?usp=sharing
3. I like to sort my holdings in % based on diff sectors. What’s the best way to create this in your view?
Rgds,
Hi FPL,
Thank you for sharing your spreadsheet. As you may have already noticed, the bloomberg feed seems to have broken in google sheets since 16 September 2020. Can i suggest that for the SG stocks that are prone to breaking, we have multiple source feeds and using Iferror formula to follow a source hierarchy? I can propose using Yahoo Finance as the 2nd source. Currently, the below works for me. I tried finding a few other data sources such as SGX, CNBC, trading view etc but they failed to work for me.
=IMPORTXML(“https://finance.yahoo.com/quote/ES3.si/”,”//span[@class=’Trsdu(0.3s) Fw(b) Fz(36px) Mb(-4px) D(ib)’]”)
Hi Bryan! Thank you for bringing this up! I’ve noticed that the sheet breaks sometimes and I had to manually fix it. It’s getting hard to find data sources for SG stocks that work consistently unfortunately… I’ll look into your suggestion and put it into the sheet maybe tomorrow or during the weekend! Thank you very much for giving me this suggestion!
I noticed that the formula for Bloomberg stopped working too and the Yahoo Finance formula doesn’t work for me. If you have a solution, it will be good to know.
Hi both, yes I realised the yahoo finance one broke today too. I have 2 auto feed and then 1 manual override where I try to have the last listed price so where all else fails, there is at least a manual input. Which may be outdated but does not cause error. Or can set to zero then manually add back for those days with missing data.
Hey Bryan, Sharam & Chandler – Looks like bad news, I’ve been looking around including paid solutions like AlphaVantage API which used to work – and they’ve all seem to no longer support Singapore tickers as far as I can tell. What used to return nice data on open price, closing price, volume, etc. is now returning blanks. Seems like we will end up having to manually update the Singapore ticker prices everyday into the spreadsheet. In my case, it’s not so bad since I only have STI ETF, but if your portfolio is holding a lot of Singapore tickers, it will be super tedious. Not sure if you guys were able to find any other alternatives that works?
I found another solution for STI ETF. Not sure if this is a permanent fix but this goes straight to the SPDR website and pulls the closing price of each day.
=value(right(index(IMPORTHTML(“https://www.ssga.com/sg/en/individual/etfs/funds/spdr-straits-times-index-etf-es3″,”table”,13),2,2),4))
so far haven’t been able to find a solution for other tickers…
Thanks much, Bryan and FPL. Yes, the Bloomberg price feed has gone – my spreadsheet pretty much useless now for tracking !
I copy pasted Bryan’s formula and replaced the quotation marks with the keyboard quotation marks but it does not work for me.
Look forward to a solution (even a non-free solution) which pulls price data for equities, bonds from multiple exchanges although my investments are mostly SGX securities and bond funds.
Found this source that seems to work for past few days
=IMPORTXML(“https://www.cnbc.com/quotes/?symbol=STTF-SG”,”//*[@id=’cnbc-contents’]/div/div[3]/div[3]/div[1]/div[2]/div/div[1]/div/table/tbody/tr[5]/td[1]/span[1]”)
Thanks Bryan! This works for me, I’ll update the template with this for now. Hopefully it continues working! Also when copy pasting formula, WordPress changes the quote symbols to the wrong one and so if somebody copies the formula from your comment it won’t work. I fixed the formula and put it into the code tags to maintain the correct quote symbols:
=IMPORTXML("https://www.cnbc.com/quotes/?symbol=STTF-SG","//*[@id='cnbc-contents']/div/div[3]/div[3]/div[1]/div[2]/div/div[1]/div/table/tbody/tr[5]/td[1]/span[1]")Hey Bryan,
The CNBC function stops working today. Do you happen to have another solution that you are using?
Thanks Bryan for the effort !
This part of the formula, viz //*, shows up as an error with an error message ” We cannot parse this operator in this context”
All Greek to me, but perhaps someone understands and can suggest the correction.
Thanks!
Hi Sharm and Bryan!
I got this working by changing all the quotes to the correct quote letter. I think the WordPress comments changes them into the wrong symbol (both the single quote and double quotes) so you just have to copy paste the quote and replace the quotes ourselves.
Let’s see if I put the formula into code tags, it would work or not:
=IMPORTXML("https://www.cnbc.com/quotes/?symbol=STTF-SG","//*[@id='cnbc-contents']/div/div[3]/div[3]/div[1]/div[2]/div/div[1]/div/table/tbody/tr[5]/td[1]/span[1]")Hi. CNBC stock price tracking seems to have stopped working. Any suggested workarounds?
Hi Bryan,
As of today (15/01/2021) the CNBC workaround seems to have stopped working – I guess they updated their website to display the price in an image instead of as text, which is why it’s not retrieving any more? Do you have the same issue, or any suggestions how to fix? Thanks!
Hello,
For anyone looking for a workaround as of 15/01/2021, I’ve found 2 ways to pull prices:
Yahoo Finance: =IMPORTXML(“http://sg.finance.yahoo.com/quote/”&A1&”/”,”//div[contains(@id, ‘quote-header-info’)]//span[@data-reactid=’32’]”)
where A1 would contain the name of the SGX ticker (e.g. G3B.SI etc.)
CNBC:
=IMPORTXML(“http://www.cnbc.com/quotes/STTF-SG”,”//div[contains(@id, ‘MainContent’)]//span[@class=’QuoteStrip-lastPrice’]”)
Hope this works for everyone. Thanks.
Thank you, the solutions worked!
Both the above solutions shared by Zain doesn’t seem to work for me unfortunately as I am getting an #ERROR! for both Yahoo and CNBC options. For Yahoo, I did change the A1 to reflect the cells in which I’ve inputted the relevant tickers.
@Damien did you have to make any further tweaks to make them work?
I found this on a Seedly forum, it works for me:
=IMPORTXML(“https://sg.finance.yahoo.com/quote/G3B.SI”,”//span[@class=’Trsdu(0.3s) Fw(b) Fz(36px) Mb(-4px) D(ib)’]”)
Hi Adrian,
Directly copying and pasting will cause errors due to how the quotation marks (” and ‘) are displayed wrongly. You just have to paste the formula, then manually replace the ” and ‘ characters. Means you backspace each ” and ‘ type them in manually. Hope that doesn’t sound too confusing.
I suspect there’s some formatting errors when copying and pasting from this comment section. Go to https://seedly.sg/questions/is-there-a-way-to-send-in-live-delayed-sgx-stock-prices-to-google-sheets and see the first comment, then copy and paste from there. Change ME8U.SI to G3B.SI, that worked for me
Directly copying and pasting will cause errors due to how the quotation marks (” and ‘) are displayed wrongly. You just have to paste the formula, then manually replace the ” and ‘ characters. Means you backspace each ” and ‘ type them in manually. Hope that doesn’t sound too confusing.
Thanks Ed! This solves the issue. Cheers!
Hi Firepathlion!
I’m having trouble with the XIRR portion as nothing is showing up at the moment. Could you help me take a look please?
Hi there,
Thanks for putting this together. I appreciate it. Just want to check if you notice that the summary tab gets a lot of errors when you buy and then sell an entire position?
I guess you don’t encounter this much because you buy and hold?
Hey everyone, just sharing a IMPORTXML code that i found on the internet (after Bloomberg, CNBC, Yahoo, Investing all stopped working for me) that works to import SGX prices into Google Finance :
=index(ImportXML(“https://sgx.i3investor.com/servlets/stk/D05.jsp”, “//td[contains(@class, ‘big16’)]”), 1, 1)
Above example is for DBS stock price.
Replace Stock Code D05 in above to another stock code such as O39 to get OCBC price, as such :
=index(ImportXML(“https://sgx.i3investor.com/servlets/stk/O39.jsp”, “//td[contains(@class, ‘big16’)]”), 1, 1)
Hi Lion! I’m having some problems with the summary tab. Would it be possible to take a look? For some reason the formula does not work and returns #NAME?
https://docs.google.com/spreadsheets/d/1ZhltV_DpcFgwJMaYNAMSlMDNwxwvInn2LBkMvVzwgAE/edit?usp=sharing
Hello! Thanks for this amazing template. But i seem to have some issues with the Charting? I followed your instructions of deleting every row except the last, but somehow the script doesn’t run automatically before midnight. Is there a trigger i have to set or…?
Hi Stan – maybe I can help, you need to configure the rule yourself.
Open the google sheets excel – navigate to “Extensions” (top of the menu bar) > “Apps Script”. This would open a new page, and in that page, hover over the left side bar, click on the icon 3rd icon from the top, which says “Triggers”.
Choose which function to run = “UpdatePortfolioValue”
Choose which deployment should run =”Head”
Select event source = Time Driven
Select type of time based trigger = Day Timer
Select time of day = Midnight to 1am (you can change to other timings if you prefer especially if u trade extensively on different market timings, but I personally went with this every midnight)
Hope this helps! 🙂
Hello all, once again the workaround for SGX prices seem to stop working. Does anyone have another workaround?
Am using the following:
=iferror(index(ImportXML(“https://sgx.i3investor.com/servlets/stk/”&A1&”.jsp”, “//td[contains(@class, ‘big16’)]”), 1, 1),REGEXEXTRACT(index(IMPORTHTML(“http://www.shareinvestor.com/fundamental/factsheet.html?counter=”&A1&” .SI”,”table”,5),1,1),”[0-9]*.[0-9]+[0-9]+”))
Try this:
=MID(REGEXREPLACE(index(IMPORTHTML(“https://www.shareinvestor.com/fundamental/factsheet.html?counter=C6L”,”table”,5),1,1),”^d”,””),14,5)
Where C6L is the ticker you are looking for
Hi guys, on the summary sheet one of the individual XIRR shows #NUM while all the other XIRR works. Anyone knows the reason why?
This is likely due to negative returns. Try adding another parameter after the last one in the XIRR formula like -0.5 or -0.6 to see if the calculation returns a valid negative returns value.
Sorry for the late reply, I added -0.5 at the end and it does return a negative value but the issue has now spread to two other ticker. Appreciate if u have any solution thanks!
Does the same solution work for the other 2 as well?
Yep it works for all now, thanks for help!
Sure thing! Glad that worked!
Hi FPL and all,
In my Charting tab, the Daily Average (XIRR) and Daily Average (Gain/Loss) columns return #N/A with the error message “When evaluating GOOGLEFINANCE, the query for the symbol: ‘VWRA’ returned no data.” — but it still works when I check googlefinance. Do you guys know how to solve this or have a workaround? Thanks very much!
I think this just may just be an issue with GSheet. Try refreshing the tab and that should work again.
Hey FPL!
Thank you for replying! Unfortunately it’s been #N/A since 13 Sep 2021, and refreshing doesn’t work.. 🙁
Hey, have you tried “LON:VWRA” instead of just “VWRA”?
When I say VWRA I mean LON:VWRA, it is listed in the London Stock Exchange, thus the LON in front 🙂
Hi FPL,
Just wanted to say that this is a great tracker that i’ve been using for a long time now. One question i have is regarding SELL trades in the “Trades” tab. I noticed that when selling something at a higher price relative to your original cost basis, the DCA price actually adjusts down vs. what is your “true” DCA cost. Is there any way to fix this? Given when selling something for a profit, it should not affect your original cost basis. Would really appreciate any help!
For anyone who wants the script to run at a specific time (I set mine to 11.59pm), I changed the script to the following:
/**Source for running at exact time: https://stackoverflow.com/questions/64083978/its-possible-run-google-sheets-script-exact-time-everyday */
/**
* This function is responsible for deleting the previous triggers and creating a new trigger for UpdatePortfolioValue).
* The only thing you need to do is to setup a daily trigger for that function before 7am, for example 6-7am.
* The following functions are just helpers, you don’t execute them (manually).
*/
function setTrigger() {
deleteTriggers();
scheduledTrigger(23,59);
}
/**
* this function accepts the time (hours and minutes) and it is responsible for
* creating the scheduled trigger for UpdatePortfolioValue
*/
function scheduledTrigger(hours,minutes){
var today_D = new Date();
var year = today_D.getFullYear();
var month = today_D.getMonth();
var day = today_D.getDate();
pars = [year,month,day,hours,minutes];
var scheduled_D = new Date(…pars);
var hours_remain=Math.abs(scheduled_D – today_D) / 36e5;
ScriptApp.newTrigger(“UpdatePortfolioValue”)
.timeBased()
.after(hours_remain * 60 * 60 * 1000)
.create()
}
/**
* This function deletes all the current triggers (in this case only one) for UpdatePortfolioValue()
*/
function deleteTriggers() {
var triggers = ScriptApp.getProjectTriggers();
for (var i = 0; i < triggers.length; i++) {
if ( triggers[i].getHandlerFunction() == "UpdatePortfolioValue") {
ScriptApp.deleteTrigger(triggers[i]);
}
}
}
/**
* Contains the code that you want to execute everyday at 11.58pm.
*/
/**
This function should be scheduled to run once per day to
update the "Charting" sheet with the current day's
portfolio statistics and thus update the chart on a
daily basis.
*/
function UpdatePortfolioValue() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Charting'), true);
spreadsheet.getRange('A1').activate();
spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();
var currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.NEXT).activate();
currentCell.activateAsCurrentCell();
spreadsheet.getActiveRange().copyTo(spreadsheet.getCurrentCell().offset(1, 0).activate(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.NEXT).activate();
spreadsheet.getActiveRange().copyTo(spreadsheet.getCurrentCell().offset(-1, 0).activate(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
};
/**
This is a custom function to stitch a new value to the
end of an array. Mainly used in conjunction with XIRR
function so I can stitch Today's date and Today's
portfolio value to the end of a list of cashflow.
*/
function STITCH(old_array, new_element) {
if (old_array.map) {
var new_array = old_array
new_array.push(new_element)
return new_array
}
else {
return 0
}
};
————-
You will also have to edit the trigger to be:
Choose which function to run = “setTrigger”
Choose which deployment should run =”Head”
Select event source = Time Driven
Select type of time based trigger = Day Timer
Select time of day = 10pm – 11pm