Skip to Content

Updates to the Free Google Stock Portfolio Tracker

I created a Stock Portfolio Tracker 5 years ago that allows you to track your portfolio based on transactions and update their prices automatically. It has since become rather popular.

You can check out my current portfolio, using this portfolio tracker here.

To use it, you can tune in to the instructions here.

There have been some developments recently, and I would like to update the people who are supporting this Google Spreadsheet.

SGX Stopped Supporting Google Finance

The first thing is that SGX have stopped allowing investors to use GoogleFinance() to get update to date last trading price information of their stocks.

This affected many folks including myself who rely on GoogleFinance to pull latest stock prices.

This means that you either have to rely on manually entering the prices on a frequent basis, or rely on Yahoo Finance.

Problem with Price Retrieval of Yahoo Finance

Prices at Yahoo Finance does work, it is just that the ImportData() function in Google Spreadsheet is having an issue.

Update (2nd April 2016): As of today, I realize that it is partly a Google Spreadsheet issue, but also my implementation of Yahoo Finance issue.  What you need to know is that, if you are using an existing spreadsheet, things will go back to normal. If you want it to be more robust, go to Yahoo Data Ref, Yahoo Data Ref USD, Yahoo Data Ref HKD. At cell A2, instead of using finance.yahoo.com in the formula, use download.finance.yahoo.com instead. If you really like the below implementation, you can go ahead and change it. I have incorporate some tips from users to improve the robustness.

You will get a Result was not expanded automatically, please insert more rows and columns Error.

This affects all the Yahoo Data Ref, Yahoo Data Ref USD, Yahoo Data Ref HKD sheets, which is where the stocks pull the prices from. I tried numerous ways and was not able to resolve it.

So I decided to put a different implementation.

I do not have time to think through this thoroughly due to some personal issues, but nevertheless this is my solution.

For those who are new to the Stock Portfolio Tracker, you can make a copy of the Google Spreadsheet to start using. It will work.

For those who are using the existing spreadsheet, here is some of my guide to how to get it working again.

1. Create functions to download Yahoo Data

Instead of using Yahoo Data Ref, we will now download price data for individual cells.

In your Google Spreadsheet, go to Tools > Script Editor….

You will come to this script editor.

In the code.gs text space, copy and paste the following (the image above and below code syntax is old, i have since improve it. Don’t worry if it looks different from the image):

function getYahooFinanceLastPrice(symbol) {
  var value = UrlFetchApp.fetch("http://download.finance.yahoo.com/d/quotes.csv?s=" + symbol + '&f=l1&p=.csv').getContentText();
  return parseFloat(value);
}

function getYahooFinance52WkLow(symbol) {
  var value =  UrlFetchApp.fetch("http://download.finance.yahoo.com/d/quotes.csv?s=" + symbol + '&f=j&p=.csv').getContentText(); 
  return parseFloat(value);
}

function getYahooFinance52WkHigh(symbol) {
  var value = UrlFetchApp.fetch("http://download.finance.yahoo.com/d/quotes.csv?s=" + symbol + '&f=k&p=.csv').getContentText(); 
  return parseFloat(value);
}

function getYahooFinance50DMA(symbol) {
  var value = UrlFetchApp.fetch("http://download.finance.yahoo.com/d/quotes.csv?s=" + symbol + '&f=m3&p=.csv').getContentText(); 
  return parseFloat(value);
}

function getYahooFinance200DMA(symbol) {
  var value =  UrlFetchApp.fetch("http://download.finance.yahoo.com/d/quotes.csv?s=" + symbol + '&f=m4&p=.csv').getContentText(); 
  return parseFloat(value);
}

 

Then click on the Disk icon to save the functions in the script and then click on the Run button.

2. Update and Propagate Yahoo Price with new Function

Once you have saved the script, and click Run, you will be able to use the functions.

Go to Stock Summary.

At the first cell below the header, or cell G2, titled Yahoo Price, change the formula to the following:

=getYahooFinanceLastPrice(E2)*1

 

Next Drag the bottom right blue box downwards to propagate the updated formula to all the rows.

Do this for all Stock Summary sheets.

2. Update and Propagate 52 Week High, Low, 50 Day MA and 200 Day MA with new Function

Next we will do the same for the 52 Week Low, 52 Week High, 50 Day MA and 200 Day MA.

Update the cells with the following formula –

V2 (52 Wk Low):

=iferror(if(AND((I2-getYahooFinance52WkLow(E2))/I2>-Ref!$C$4,(I2-getYahooFinance52WkLow(E2))/I2<Ref!$C$4),"Near",if((I2-getYahooFinance52WkLow(E2))/I2>0,"Above",if((I2-getYahooFinance52WkLow(E2))/I2<0,"Below","ERR"))),"ERR")

W2 (52 Wk High):

=iferror(if(AND((I2-getYahooFinance52WkHigh(E2))/I2>-Ref!$C$4,(I2-getYahooFinance52WkHigh(E2))/I2<Ref!$C$4),"Near",if((I2-getYahooFinance52WkHigh(E2))/I2>0,"Above",if((I2-getYahooFinance52WkHigh(E2))/I2<0,"Below","ERR"))),"ERR")

X2 (50 Day MA)

=iferror(if(AND((I2-getYahooFinance50DMA(E2))/I2>-Ref!$C$4,(I2-getYahooFinance50DMA(E2))/I2<Ref!$C$4),"Near",if((I2-getYahooFinance50DMA(E2))/I2>0,"Above",if((I2-getYahooFinance50DMA(E2))/I2<0,"Below","ERR"))),"ERR")

Y2 (200 Day MA)

=iferror(if(AND((I2-getYahooFinance200DMA(E2))/I2>-Ref!$C$4,(I2-getYahooFinance200DMA(E2))/I2<Ref!$C$4),"Near",if((I2-getYahooFinance200DMA(E2))/I2>0,"Above",if((I2-getYahooFinance200DMA(E2))/I2<0,"Below","ERR"))),"ERR")

Next Drag the bottom right blue box downwards to propagate the updated formula to all the rows.

Do this for all Stock Summary sheets.

3. Delete Yahoo Data Ref, and similar sheets

Now that your Stock Summary sheets update the price themselves, there is no need for the Yahoo Data Ref sheets. You can remove them

Summary

Hope this solves your problem, and if you have a better implementation do let me know.

Kyith

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Nicholas

Friday 20th of March 2020

Hello, not sure if the list of getYahooFinanceLastPrice scripts still works in 2020? Ive been trying to get it to work with google sheets for 4hours now but the function doesnt seem to come up on google docs even after I save and run it.

Running it in Google app also gives me this error: Exception: DNS error: http://download.finance.yahoo.com/d/quotes.csv?s=undefined&f=l1&p=.csv (line 2, file "Code")Dismiss

Not sure if its related to this? https://github.com/RxJSInAction/rxjs-in-action/issues/13

best, Nic

Kyith

Saturday 21st of March 2020

Hi Nicholas, there have been some changes in recent years. Yahoo stop providing these API but things should still work in an alternative way.

You can refer to the comments in this post: https://investmentmoats.com/uncategorized/yahoo-finance-data-shuts-down-my-modification-to-my-stock-portfolio-tracker/

Or you could read my solution here: https://investmentmoats.com/uncategorized/automatically-yahoo-finance-stock-price-google-spreadsheet/

Prasad

Saturday 13th of October 2018

hello I am using your "Stock Portfolio Tracker"

I am able to get SGX Stock prices, thank you so much.. But I am unable to get the prices for Funds such as Schroder Asian Income SGD, First State Bridge A SGD, PIMCO Income Fund SGD (Hedged), etc..

Can you guide me through, please.

Kyith

Saturday 13th of October 2018

Hi Prasad i think you would need to find some live feed for that. if not i cannot help much

Clarence

Friday 3rd of November 2017

=arrayformula(ImportData("http://download.finance.yahoo.com/d/quotes.csv?s="&LEFT(CONCATenate('Stock Summary'!F2:F),LEN(CONCATenate('Stock Summary'!F2:F))-1)&"&f=snp2l1jkm3m4"&"&"&'Read This First'!$B$5))

Apparently the above formula is not working anymore, keeps showing N/A even though i remove the s for the http

Kyith

Saturday 4th of November 2017

Hi Clarence, Yahoo may have finally stop people from using this. All the excel, google spreadsheet and software relying on their prices will not work, including mine. Here are more info, especially if you are on Singapore stock exchange > https://investmentmoats.com/uncategorized/yahoo-finance-data-shuts-down-my-modification-to-my-stock-portfolio-tracker/

Clarence

Friday 3rd of November 2017

Hi Kyith, do you have any idea how do i solve this? i'm unable to pull the last price using =getYahooFinanceLastPrice(E2)*1

This is the error message when i tried to run script for pulling last price Request failed for http://download.finance.yahoo.com/d/quotes.csv?s=undefined&f=l1&p=.csv returned code 403. Truncated server response: Yahoo! - 403 Forbidden -- error 403It has come to our attention that this service is being used in violati... (use muteHttpExceptions option to examine full response). (line 88, file "Code")

Mark Achurch

Saturday 17th of June 2017

Hi Kyith,

love your work, I have done the last updates i could see, the only thing not working is the portfolio history page, im doing it manually for now, any chance you would know how to fix it? i live in Australia, Thanks in advance.

Kyith

Saturday 17th of June 2017

the portfolio history will be updated based on a script. Go to Tools > Script editor.....

You can use the drop down to find the function copyLivePortfolioDataToHistory(). Click on current project triggers, and set a timer of 1 time per day.

This site uses Akismet to reduce spam. Learn how your comment data is processed.