Tuesday, February 12, 2013

Yahoo Finance's Stock Quotes with Google Spreadsheet

Online Document Solution for Stock Quotes

The web technology brings us to work online which is now becoming more convenient, efficient, and easy. You can work from anywhere as long as you are connected to the internet. You don't have to worry if you don't bring your laptop when traveling. Just go to any internet coffee, open your files that you stored in the cloud.

There are two most popular document solutions available on the web, Microsoft Sky Drive and Google Drive. I use both for document solution, in this case spreadsheet, which are coming for the two giants: Microsoft Excel Web App and Google Spreadsheet.

Microsoft Excel web app is good enough if you only store your excel files to the cloud via Outlook.com. You can share your files, and do basic editing, including using simple standard formula. The limitation is you can't run your VBA script in you online spreadsheet.

Before the cloud era, I always work offline and use Excel file, saved in my desktop or notebook, to keep and record my stock transactions, and monitor stock prices. I use VBA scripts to retrieve stock information. The feature I use is simple, retrieve latest stock prices from Yahoo Finance data in one sheet, and I manage stock transaction in another sheet.

After the cloud technology available (and for free), it becomes annoying that I have to open my desktop (go home) or laptop every time I want to check my investments.

So I did few research and start developing script for this task. I use Google Spreadsheet since it the only cloud based spreadsheet that supports Javascript that can perfectly to handle the task.

This article explains in simple way how to retrieve Stock Price (and other data) from Yahoo Finance, populate it in excel, so you can use it in your online spreadsheet file.

Yahoo Finance provides everything you need for your stock information (and other finance data) and its free. The data is delay about 20 minutes which is fine for my needs. Google Finance has similar service but limited to stock market in the US.

Preparing Spreadsheet File

You must have Google account to use Google Spreadsheet. Login to Google, then open Google Drive. Click Create button, and choose Spreadsheet. A new Spreasheet tab will be opened. Rename the file to "Stock Price".

In Google docs you don't have to worry to save your file, Google will do automatically for you every time you change the file.

Create The Stock Information Script

Now let's start the fun part. If you are not a programmer, don't worry. I will explain as simple as possible.

The script we are creating will perform simple two tasks. It will read stock symbols that we filled in the spreadsheet, and read the parameters of what information do we want to load. In this article we only load Price, Open, Change, High, and Low stock information. You can add more parameters later once you understand the basic.

Click Tools from menu bar, then select Script Manager. Under Create Script for, select Spreadsheet. Copy the code below and paste to your script. Then save your script file as StockInfo.


function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [ {name: "Get Stock Data", functionName: "getStock"} ];
  ss.addMenu("Stock", menuEntries);
}

function getStock() {
  var YahooUrl;
  var row = 2;
  var stockdata;
  var tempdata = new Array(); 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.setActiveSheet(ss.getSheets()[0]);
  
  sheet.getRange("B2:M40").clearContent();

  while (true) {
    if (!sheet.getRange(row, 2).getValue()) {
      var symbol = sheet.getRange(row, 1).getValue();
      var j = 2;
      if (!symbol || row == 41) break; // break if empty symbol or limit the queary to max 40 rows.
      Logger.log("Updating symbol: " + symbol);
      
      YahooUrl = UrlFetchApp.fetch("http://quote.yahoo.com/d/quotes.csv?s=" + symbol + "&f=l1o0c1h0g0"); // Change parameter f for other information
      stockdata = YahooUrl.getContentText()
      tempdata = stockdata.split(",");
      
      for (a in tempdata ) {
        sheet.getRange(row, j).setValue(parseInt(tempdata[a]));
        j++;
      }
    } else {
      Logger.log("Updated completed: " + symbol);
    }
    row++;
  }
  
}


The first function onOpen() is a function to setup a menu called "Get Stock Data" in the spreadsheet that will retrieve stock data by calling getStock() function.

The getStock() function is a function to retrieve data from Yahoo Finance, I use UrlFetchApp function, put the Yahoo Finance URL string which require stock symbol and information parameter. The symbol of stock should be typed in the first column in first sheet. The script is limited to process only up to 40 symbols. In my experience, retrieving 40 symbols is still within acceptable processing time.

You can change the stock information retrieved, by changing the "&f" parameters. See the detail reference of the parameters here.

Activate the Script

Go back to Stock Price spreadsheet file that we just created. From the menu, select Tools, then select Script Manager. The Script Manager window will be opened, and you will see the script we have created. Click onOpen script from the list, then click Run button at the left bottom of the Script Manager.



In the blank sheet1, you have to type all stock symbols that you would like to query using Yahoo standard stock symbol format. All symbols must be filled in first column starting from second row. The first row we use as title row.

In this article, parameters that we use are l1o0c1h0g0. Type the title for each parameter at the first row starting from cell A2: Price, Open, Change, High, Low.



If you follow the steps above correctly, you will see a new menu called Stock. Click it, then select Get Stock Data. It will run the getStock() function and feed the cells with stock information.

With Yahoo Finance Quotes, you can load not only stock information from 92 different markets. Try and experiment yourself to retrieve other finance data and use different parameters.

Conclusion

I have been using this solution for almost a year. It works perfectly for me. I can check stock information, plan my investment, record transaction in my online spreadsheet from anywhere. The only problem is Google Spreadsheet with scripting feature does not work on mobile version, only works on desktop version. I always use desktop version from my iPad. Now I never open the offline Excel version.

That's all, I hope you find it useful. Don't forget to post your comments or input below.

13 comments:

Anonymous said...
This comment has been removed by a blog administrator.
Anonymous said...
This comment has been removed by a blog administrator.
Anonymous said...
This comment has been removed by a blog administrator.
Anonymous said...
This comment has been removed by a blog administrator.
Anonymous said...
This comment has been removed by a blog administrator.
Anonymous said...

Why do the values show as integers only, no decimals show up?

Stochastic Oscillator said...

Thanks for great information you write it very clean. I am very lucky to get these tips from you.
Stochastic Oscillator

Anonymous said...

Your instructions were clear and populated my list of stocks. My question is can we get the stock prices to 2 decimal places?

Appreciate you post.

Anonymous said...

If you're using Google Spreadsheet, this is much easier to do using the GoogleFinance() function.

BIGPROFITBUZZ said...

BigProfitbuzz is an Indian stock market advisory firm. BigProfitbuzz proven month after month that trading and investing in stock market can be profitable whether market is bull or bear. As said last time we made a sell Position in NIFTY. All our paid clients made a very good profit. Now weBSE, STOCK TIPS suggest all our traders to make a buy position in NIFTY around 6100-6150 with stoploss of 5950 for the target of 6400-6500.One can also make a buy position in NIFTY 50 stocks according to NIFTY levels. If want good calls in the market then fill our trial form & get them. We believe in Low risk for sufficient profit.
Regards
BIGPROFITBUZZ TEAM

Brendan said...

This is indeed a great script. As the others have mentioned, is there any way we can get decimal places added to the resulting stock prices?

Damien Thibault said...

Hello,

Why if i add n0 on f parameter the response is #NUM! ?

YahooUrl = UrlFetchApp.fetch("http://quote.yahoo.com/d/quotes.csv?s=" + symbol + "&f=l1o0c1h0g0s6n0");

Thank's for your response

Harz said...

I use this. It works great for me.
Stock Quotes in Excel