Solution to Yahoo Finance Data Not Refreshing in Google Spreadsheet
There have been a lot of people emailing me, messaging me, finding ways to get hold of me, to tell me that my FREE Stock Portfolio Tracker is not working.
I thought if I don’t try to solve this, there will be no good sleep for me.
So here are some explanations and how to get around this problem.
Yahoo Finance Data is still Working
Verizon’s purchase of Yahoo is going through, and we do not know how it will affect Yahoo Finance.
However, Yahoo is still able to let us access to the data.
The Yahoo Price in Stock Portfolio Tracker comes from the sheets Yahoo Data Ref, Yahoo Data Ref USD, Yahoo Data Ref HKD.
The Cell A2 in each of those sheet contains the formula that pulls the prices of stocks in your Stock Summary, Stock Summary USD, Stock Summary HKD.
If you look at the image, it shows “#NA” basically it does not work. Hence in Stock Summary you cannot see the share price.
If you past the formula “http://finance.yahoo.com/d/quotes.csv?s=cc3.si&f=snp2l1jkm3m4” in a new browser, you will realize a CSV file with Starhub’s share price is still downloaded (CC3.SI is Starhub’s stock quote)
What is Not Working is the Google Spreadsheet Refresh
Google Spreadsheet maintains a cache or stored cell result. If it seems that you did not make a change to the cell, it will not auto-recompute the cell.
In our current implementation, Yahoo Data Ref, Yahoo Data Ref USD, Yahoo Data Ref HKD grabs the latest list of stock quote from Stock Summary and sends it to Yahoo to get the price data.
If we do not change the list of symbols, that cell A2 will not recompute.
The easy solution to this is to send something into the importData function that always changes.
You have functions like Now() and Rand() that gives the time at this moment and a random number respectively. As we are always sending a new value with these two functions, this should work.
However, Google is rather clever about this, and they know you will do that, so they do not permit any function that uses now(), Rand(), random() to work.
The Short Workaround
So it is a refreshing problem.
This means that you can refresh yourself. How?
By changing the formula in cell A2 in Yahoo Data Ref, and then changing it back. Doing this will ask ImportData to re-grab the CSV data again.
Suppose your starting state is like this:
Step 1: Type an “s” behind the http. If your starting state is https, remove the “s” instead:
Step 2: Press the “Enter” key to change the formula in cell A2:
The data appears.
To revert and ensure the formula is coherent, remove the “s” or add the “s” if its not there:
Press the “Enter” key:
You will realize the data is still there.
The More Permanent Solution
You will realize that the workaround method, is not very permanent. That will be a problem.
I been trying to come up with a solution and did a lot of refreshing of the cell A2 but they all prove useless.
This last one seem to last for a long time.
The solution that I came out is on a similar concept.
- We will have a field that contains a date time string
- We will have a function in script that refreshes #1
- When we came ImportData in Yahoo Data Ref we will send the field in #1
In this way, #1 is constantly changing and cell A2 in Yahoo Data Ref will work.
For those who are new to Stock Portfolio Tracker
If you make a copy of my Google Spreadsheet and use it as your own, you will need to set the trigger to refresh the field that contains the date time string.
Step 1: Go to Tools > Script Editor…
Step 2: Click on the clock to the left
This will bring up the panel.
Step 3:Add a new Trigger, assign refreshReadMeFirstTime, Minutes Timer and set the interval to every 5 minutes:
Step 4: Click save
Step 5: Go to Read Me First sheet to observe if cell B5 changes. Then see if your Stock Summary sheets will update:
For those using an existing version of Stock Portfolio Tracker
You will need to copy some things over to your existing Google Spreadsheet
Step 1: Make a copy of my version of Stock Portfolio Tracker using File > Make a copy…
Also go to Tools > Script Editor… When my version is launch.
Step 2: in Your version if Stock Portfolio Tracker, go to Read Me First sheet.
Insert a new row between Row 4 (Description) and Row 5 (Donate). This should eventually be row 5.
Copy cell A5 and B5 from my version to your version. It should look like this.
Step 3: in My version of Script Editor, copy the function refreshReadMeFirstTime() to your Script Editor. It should look like this in your version:
Click the Save button (that looks like a floppy disk)
Step 4: in your version of Script Editor, click on the Clock icon to launch the Triggers menu.
Add a new Trigger, assign refreshReadMeFirstTime, Minutes Timer and set the interval to every 5 minutes:
Step 5: Modify the cell A2 in your Yahoo Data Ref, Yahoo Data Ref USD and Yahoo Data Ref HKD.
Add &“&”&‘Read This First’!$B$5 to the back part of the parameter for ImportData. It should look like this:
What you are doing is appending the date time string in cell B5 in Read Me First Sheet, into the Yahoo Finance data retrieval process. If you do correctly and press Enter, you should see that you are able to retrieve the data like what is shown in the image above.
Do the same for Yahoo Data Ref USD and Yahoo Data Ref HKD if they are in use.
Step 6: if you done everything correctly, the cell B5 in Read me first sheet should change.
That should trigger a change in Yahoo Data Ref.
Over time, it should not show blank anymore
Not the most elegant solution but it looks like it works.
I tested it for 2 days and this solution seem to work.
If you need help porting, even after this guide, my support is open at email@example.com.
I do levy a support charge of USD10.00 through Paypal. Email me and I will guide you through the details.