Ikai Lan says

I say things!

Apps Script quick tips: building a stock price spreadsheet

with 4 comments

I’ve been using iGoogle less and less over the past few years. A few weeks ago, the team announced that iGoogle would be shutting down in November 2013. It’s not a huge loss to me, though I do check iGoogle several times a day. Why? Stock prices! I’ve been using the Stock Market gadget for years.

As it turns out, the functionality I want is very easy to replicate using Google Spreadsheets and Google Apps Script. I’m thoroughly convinced that the fastest way to wire up different Google services for custom functionality is this product. Google Apps Script provides services to access Google Finance APIs.

Knowing this, it’s incredibly easy to wire up a spreadsheet that has access to live finance data. The spreadsheet I use looks something like this:

Image

We can pull this off in a few very easy steps.

Step 1: Create a spreadsheet.

I made a spreadsheet with the following columns names:

Symbol Price Change Change % Details

Image

My intended use is to populate the Symbol column and have the rest of the data in the other columns auto populated. The nice thing about writing scripts that integrate with spreadsheets is that we have a built in UI for making edits, sorting, filtering and searching. By using spreadsheets as our data entry and manipulation UI, our functionality is already more advanced than the functionality provided in the Stock Market gadget as well as many other online portfolio-at-a-glance services.

Step 2: Create the script

What we’re going to do is write a few functions in the Script Editor. Spreadsheet cells can accept both the standard set of built-in functions that do simple things like SUM, AVG, and so forth, but they can also accept custom functions that retrieve data from other Google services.

Click Tools -> Script Editor.

Image

This will open up a new tab in your browser where you can write code. The default name of this file is Code.gs. Replace whatever is in the buffer with this:

function getStockPrice(symbol) {
  return FinanceApp.getStockInfo(symbol)["price"];
}
function getStockPriceChangePct(symbol) {
  return FinanceApp.getStockInfo(symbol)["changepct"];
}

function getStockPriceChange(symbol) {
  return FinanceApp.getStockInfo(symbol)["change"];
}

function getGoogleFinanceLink(symbol) {
  return "http://www.google.com/finance?q=" + symbol;
}


Your Script Editor should look like this:

Image

FinanceApp.getStockInfo() returns a FinanceResult instance with a LOT of data. I only care about the basics: price, price change, and price change percentage. The functions I’ve defined reflect this.

Step 3: Add the functions into the cells

Now let’s go back to the spreadsheet tab. I’ve populated a few basic symbols under the Symbol column: GOOG (Google) and AAPL (Apple), two of my favorite companies. In column B2, enter this value:

=getStockPrice(A2)

Hit enter. If everything is working correctly, this will now populate with the latest price of whatever stock symbol is in A2. Let’s add the rest of the functions. In C2, enter:

=getStockPriceChange(A2)

D2

=getStockPriceChangePct(A2)

I like to have a link back to Google Finance if I ever want to do more research on a company, so in E2, add:

=getGoogleFinanceLink(A2)

This next part is hard to explain but shouldn’t be difficult for anyone who has used a spreadsheet program before. Highlight rows B2-E2. You can hold down shift and select these rows. Now hover your mouse over the bottom right corner of E2 and drag down a few rows. What this does is it copies the functions for the subsequent rows, but it substitutes A2 for A3, A4, A5, … depending on what row you happen to be in. You can test this out by adding additional stock symbols. The live stock data will appear.

Step 4: Color Coding

I like to see color coding depending on whether a stock price has risen or fallen. Hold down shift and click on C, then D at the top of the rows:

Image

Click the arrow to the right. This should drop down a menu. Click on “Conditional Formatting”:

Image

You’ll want to add two rules: a greater than rule and a less than rule. When the Change and Change % columns are greater than 0, change the background to green. When they are less than 0, change the backgrounds to red. Click “Save Rules”

You’re done!

Summary

I’ve only scratched the surface of what can be done with Apps Script. We haven’t even gotten into a lot of the other cool things we can do. Using Clock Events, we can check every few minutes for changes and email ourselves using the GmailApp library if a stock price change is greater than some threshhold. We can generate charts based on historic data. And so on, and so forth. For more examples of things that can be done with Google Apps Script, check out the tutorials section for more ideas.

Have a great weekend!

– Ikai

 

Advertisements

Written by Ikai Lan

July 27, 2012 at 2:05 pm

4 Responses

Subscribe to comments with RSS.

  1. Looks super easy! =)

    Sergio

    July 27, 2012 at 5:13 pm

  2. Great tutorial, but it turns out that Google Spreadsheets already has some of this functionality built in:

    https://support.google.com/docs/bin/answer.py?hl=en&answer=155178

    Eric Koleda

    July 30, 2012 at 8:29 am

  3. D’oh, guess I’m going to have to implement some of that “advanced functionality” I hinted at earlier to make up for this blunder =P.

    Ikai Lan

    July 30, 2012 at 9:11 am

  4. Your script is great. However, if I put many stock in one spreadsheet, it will said I call the function too much and return an error. In the worse case, it said the script invoked the service too many times for one day and the spreadsheet will not update anymore. It ask to use a Utilities.sleep(1000); do you know how to add to your script let say by update around 15 min interval? Thanks for your help.

    Gabriel

    August 15, 2012 at 5:16 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s