Ikai Lan says

I say things!

Archive for July 27th, 2012

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

 

Written by Ikai Lan

July 27, 2012 at 2:05 pm