Skip to Content

How to Fix Your FREE Stock Portfolio Tracker Showing $-1 in their Last Price | Yahoo Stock Price 401 Unauthorized Access Issue

Many readers have updated me they are seeing “-$1” in the prices on their Stock Portfolio Tracker.

For those readers who are newer, I created a FREE Google Spreadsheet Stock Portfolio Tracker to help people track their stock securities based on transactions.

If you are a more private person and would like to track your portfolio by yourself instead of using a good third-party tool such as Stocks.Cafe, then you can check out Stock Portfolio Tracker (Free Online Investment Stock Portfolio Tracker Spreadsheet).

I did up a video to explain the problem and how to alleviate the problem:

The problem is caused by Yahoo preventing access to their API call.

But I have two implementations when I implemented the last change to the Yahoo Price updater.

One of the implementations still works.

The short version of the solution: Instead of using getYPrice3() function, use getYPrice2() function!

Now, I am not sure how long this implementation will last, but at least it works for now.

Here is the code for getYPrice2()”

function getYPrice2(symbol) {
  symbol = symbol || "D05.SI";
  symbol = encodeURI(symbol);
  Utilities.sleep(Math.floor(Math.random() * 5000))
  var url = ''+ symbol; // last one day history
  var response = UrlFetchApp.fetch(url, {muteHttpExceptions: true});
  var responseCode = response.getResponseCode();
  Logger.log("Response code:"+responseCode); 
  if (responseCode === 200) {
    var chain = JSON.parse(response.getContentText());
    return parseFloat(chain.optionChain.result[0].quote.regularMarketPrice);
     return -1; 

I am posting the code for those of you who are on very legacy implementation of the Stock Portfolio Tracker.

You might be interested to change it.

If you want to trade these stocks I mentioned, you can open an account with Interactive Brokers. Interactive Brokers is the leading low-cost and efficient broker I use and trust to invest & trade my holdings in Singapore, the United States, London Stock Exchange and Hong Kong Stock Exchange. They allow you to trade stocks, ETFs, options, futures, forex, bonds and funds worldwide from a single integrated account.

You can read more about my thoughts about Interactive Brokers in this Interactive Brokers Deep Dive Series, starting with how to create & fund your Interactive Brokers account easily.


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


Tuesday 25th of July 2023

Hey, Thank you for providing such an awesome spreadsheet for free. is there a way to modify the last price formula to go back to using google price data if yahoo fails again automatically?


Saturday 11th of November 2023

Hi Kristian, you can always go to the IF this then that cell to switch it around!


Monday 24th of July 2023

Thanks for the quick solution!


Saturday 22nd of July 2023

Thanks for providing the stocks tracker and it has been very useful for past 3 years. However after several failures, I felt that I had enough of it. I did some research online and come up with a Power Query function in Excel that can skip Google Sheets. Not sharing as I am using other websites as freeloader and may get banned. Some ideas for you to consider?

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