OUseful.Info, the blog…

Trying to find useful things to do with emerging technologies in open education

Visualising Financial Data In a Google Spreadsheet Motion Chart

Following on from Data Scraping Wikipedia With Google Spreadsheets, here’s a quick post showing how you can use another handy Google spreadsheet formula:

=GoogleFinance(“symbol”, “attribute”, “start_date”, “end_date”, “interval”)

This function will pull in live – and historical – price data for a stock.

Although I noticed this formula yesterday as I was exploring the “importHTML” formula described in the Wikipedia crawling post, I didn’t have time to have a play with it; but after a quick crib of HOWTO – track stocks in Google Spreadsheets, it struck me that here was something I could have fun with in a motion chart (you know, one of those Hans Rosling Gapminder charts….;-)

NB For the “official” documentation, try here: Google Docs Help – Functions: GoogleFinance)

# Stock quotes and other data may be delayed up to 20 minutes. Information is provided “as is” and solely for informational purposes, not for trading purposes or advice. For more information, please read our Stock Quotes Disclaimer.
# You can enter 250 GoogleFinance functions in a single spreadsheet; two functions in the same cell count as two.

So – let’s have some fun…

Fire up a new Google spreadsheet from http://docs.google.com, give the spreadsheet a name, and save it, and then create a new sheet within the spreadsheet (click on the “Add new sheet” button at the bottom of the page). Select the new sheet (called “Sheet2″ probably), and in cell A1 add the following:

=GoogleFinance(“AAPL”, “all”, “1/1/2008″, “10/10/2008″, “WEEKLY”)

In case you didn’t know, AAPL is the stock ticker for Apple. (You can find stock ticker symbols for other companies on many finance sites.)

The formula will pull in the historical price data for Apple at weekly intervals from the start of 2008 to October 10th. (“all” in the formula means that all historical data will be pulled in on each sample date: opening price, closing price, high and low price, and volume.)

(If this was the live – rather than historical – data, it would be updated regularly, automatically…)

It’s easy to plot this data using a Google chart or a Google gadget:

Google spreadsheet create chart/gadget menu

I’m not sure a bar chart or scatter chart are quite right for historical stock pricing… so how about a line chart:

Et voila:

If you want to embed this image, you can:

If I was using live pricing data, I think the image would update with the data…?

Now create a few more sheets in your spreadsheet, and into cell A1 of this new sheet (sheet3) paste the following:

=GoogleFinance(“IBM”, “all”, “1/1/2008″, “10/10/2008″, “WEEKLY”)

This will pull in the historical price data for IBM.

Create two or three more new sheets, and in cell A1 of each pull in some more stock data (e.g. MSFT for Microsoft, YHOO for Yahoo, and GOOG…)

Now click on Sheet1, which should be empty. Fill in the following title cells by hand across cells A1 to G1:

Now for some magic…

Look at the URL of your spreadsheet in the browser address bar – mine’s “http://spreadsheets.google.com/ccc?key=p1rHUqg4g423seyxs3O31LA&hl=en_GB#”

That key value – the value between “key=” and “&hl=en_GB#” is important – to all intents and purposes it’s the name of the spreadsheet. Generally, the key will be the characters between “key=” and an “&” or the end of the URL; the “&” means “and here’s another variable” – it’s not part of the key.

In cell B2, enter the following:

=ImportRange(“YOURKEY”, “Sheet2!A2:F42″)

YOURKEY is, err, your spreadsheet key… So here’s mine:

=ImportRange(“p1rHUqg4g423seyxs3O31LA”, “Sheet2!A2:F42″)

What ImportRange does is pull in a range of cell values from another spreadsheet. In this case, I’m pulling in the AAPL historical price data from Sheet2 (but using a different spreadsheet key, I could pull in data from a different spreadsheet altogether, if I’ve made that spreadsheet public).

In cell A2, enter the ticker symbol AAPL; highlight cell A2, click on the square in the bottom right hand corner and drag it down the column – when you release the mouse, the AAPL stock ticker should be added to all the cells above. Label each row from the imported data, and then in the next row, B column, import the data from Sheet 3:

These rows will need labeling “IBM”.

Import some more data if you like and then… let’s create a motion chart (info about motion charts.

Highlight all the cells in sheet1 (all the imported data from the other sheets) and then from the Insert menu select Gadget; from the Gadget panel that pops up, we want a motion chart:

Configure the chart, and have a play [DEMO]:

Enjoy (hit the play button… :-)

PS And remember, you could always export the data from the spreadsheet – though there are probably better API powered ways of getting hold of that data…

PPS and before the post-hegemonic backlash begins (the .org link is broken btw? or is that the point?;-) this post isn’t intended to show how to use the Google formula or the Motion Chart well or even appropriately, it’s just to show how to use it to get something done in a hacky mashery way, with no heed to best practice… the post should be viewed as a quick corridor conversation that demonstrates the tech in a casual way, at the end of a long day…

PS for a version of this post in French, see here: Créer un graphique de mouvement à partir de Google Docs.

Written by Tony Hirst

October 16, 2008 at 12:05 am

20 Responses

Subscribe to comments with RSS.

  1. I get an error in spreadsheets when I paste the function you mention: GoogleFinance(”IBM”, “price”, “1/1/2008″, “10/10/2008″, “WEEKLY”); surfing through the api, “all” doesn’t seem to be a valid attribute.

    damian

    October 16, 2008 at 2:02 am

  2. @damian
    Copying that formula was giving me an error too, it seems that the quotation marks in the post are a different encoding, and so a “ appears when it should be a ”
    Notice the URL posted for the documents include &hl=en_GB which designates a certain key set.
    Just type in the quotation marks manually, or try copying from here:
    =GoogleFinance(“IBM”, “price”, “1/1/2008″, “10/10/2008″, “WEEKLY”)

    @Hirst
    What was the reason for using importRange() and what advantage would there be over using a direct sheet or range reference?

    Also, would it be possible to share your example spreadsheets? This allows other users to see the spreadsheet source code using the option Share -> Share with the World, and if you “Let people view without signing in” that would be great as well. It helps to quickly see a live copy =)

    RE: “If I was using live pricing data, I think the image would update with the data…?”
    Yes it can, just make sure to enable “Automatically Republish” in Share -> Publish as a Web page.

    Mike Chelen

    October 16, 2008 at 7:13 am

  3. After the post you did yesterday, I tried to find information through the api docs or on the net in general about when a cell updates automatically in google spreadsheets, to no avail. So I rigged up a scrape of a clock to see when they occur. It turns out after using =importXml(“http://www.timeanddate.com/worldclock/city.html?n=179″, “//*[@id='ct']“) and manually checking it every now and again, it wasn’t regular, getting 27min, 60min, ?min (I was logged in to google docs and had the spreadsheet open the entire time). In the meantime I posted a question to the helpgroup to get an answer…

    Greg

    October 16, 2008 at 7:19 am

  4. @damien
    It looks like WordPress is mangling the quotes, you can see the proper code here: http://pastebin.com/f1eeac477

    Mike Chelen

    October 16, 2008 at 7:57 am

  5. @Mike Sharing now on (thought I’d done that before – oops – thanks for pointing it out :-)
    Re: using importRange() – I was just trying to give an example of how to use the formula; if it hadn’t been so late last night, I’d have set up different spreadsheets to show how data could be pulled in from them. The post isn’t so much a ‘best practice’ guide, as a ‘here are some reusable patterns/constructs for grabbing data and moving it around’.

    @Greg re the frequency of lookup – thanks for trying that :-) I wonder if the spreadsheets get updated according to a pseudo random schedule depending on how frequently they are accessed? If you get an authoritative reply back from the group, could you post it here? TIA :-)

    Tony Hirst

    October 16, 2008 at 9:20 am

  6. It seems as though a cell normally updates whenever google feels like it – at most every few hours or so. A response I received said that it is possible to trigger an update to the cell by composing any import request with a finance function that by itself will trigger an update every two or so minutes, and by modifying the original import url in such a way that it will always appear different to get around any caching of the page that google does (so you don’t keep getting old data).

    The above example turns into:
    =importXml(”http://www.timeanddate.com/worldclock/city.html?n=179″&”&blah=”&INT(NOW()*1E3)&REPT(GoogleFinance(“GOOG”);0), “//*[@id='ct']“)

    An explanation:
    http://docs.google.com/View?docid=dhrr6ms2_523cs7274fv

    Greg

    October 16, 2008 at 9:41 am

  7. Very cool (again!), thank for sharing (again!).

    chrisco

    October 16, 2008 at 10:31 am

  8. [...] Search « Visualising Financial Data In a Google Spreadsheet Motion Chart [...]

  9. @Greg Thanks for digging that stuff about cell updates up; I had half wondered about setting up a cell that generated a random number, and then constructing a URL with that random nuumber as a dummy variable, to try and generate a unique URL each time?

    Tony Hirst

    October 17, 2008 at 9:02 am

  10. Great how-to’s. FWIW, there’s a lot of campaigning potential in these tools; for example, the Sunlight Foundation has an awesome dynamic visualisation of political contributions by the finance, insurance and real estate industries, showing how they peaked as regulatory mechanisms were being dismantled.
    http://www.internetartizans.co.uk/best_bailout_responses

    dan mcquillan

    October 17, 2008 at 7:32 pm

  11. @dan a good source of campaign financial info is the New York Times campaign finance API: http://developer.nytimes.com/docs/campaign_finance_api/

    Tony Hirst

    October 17, 2008 at 9:23 pm

  12. [...] Data API Published October 17, 2008 Uncategorized Following up on a comment to Visualising Financial Data In a Google Spreadsheet Motion Chart by Dan Mcquillan: “FWIW, there’s a lot of campaigning potential in these tools”, I [...]

  13. The “post-hegemonic backlash,” heh. Keeping you on your toes? Nah, don’t worry.

    (And yes, I just realized myself that the .org link is broken… but updated credit card details have just been provided to the appropriate domain name service.)

    Jon

    October 18, 2008 at 9:54 pm

  14. Hi,

    thanks for the information in getting the stock data using spreadsheets. I would like to know if there is any way to feed the stock symbol dynamically like $symbol,
    =GoogleFinance(”$symbol”, “price”, “1/1/2008″, “10/10/2008″, “WEEKLY”)

    so that we can get the data for the particular symbol if someone select a symbol like “GOOG”.

    Is this possible? It will be helpful if we need to show many symbols and I guess it would be tough to write the above line for many symbols.

    Please let me know. You can also email me!!

    thanks
    pragan.

    PRagan

    November 7, 2008 at 3:35 pm

  15. “like to know if there is any way to feed the stock symbol dynamically like $symbol,
    =GoogleFinance(”$symbol”, “price”, “1/1/2008″, “10/10/2008″, “WEEKLY”)”

    You could feed it in from another cell:
    =GoogleFinance(B4, “price”, “1/1/2008″, “10/10/2008″, “WEEKLY”)

    That cell could be on the same – or differnt – sheet in the same spreadsheet, or in another public spreadsheet (bring the continue into e.g. B4 using =importRange)

    Tony Hirst

    November 7, 2008 at 5:16 pm

  16. Hi Tony, thanks to your amazing explanation above – I have now taken some live ‘rates’ financial data and embedded in a google spreadsheet. Now that Many Eyes wikified is public, I’ve got as far as putting that into a data page: http://manyeyes.alphaworks.ibm.com/wikified/Nicola/LiveTest_Rates

    Haven’t had a chance to look at visualizing it yet re the various Many Eyes options. I will know by mid-day tomorrow once Libor rates have been updated whether the live element will have worked or not.

    Thank you so much for doing this, I would never have been able to do something like this myself and you are still officially a genius :-))))

    Nicola

    racingshoes

    February 24, 2009 at 1:31 pm

  17. Meant to add – I am aware of potential copyright issues with getting financial data – this data is only up for a temporary test – but I think I get enough overall indices and rates data from Yahoo Finance (can’t seem to find everything for this on Google Finance)

    Nicola

    racingshoes

    February 24, 2009 at 2:07 pm

  18. @nicola I hadn’t seen Many Eyes wikified – thanks for the tip off… that’s one more piece of the jigsaw in place ;-)

    Tony Hirst

    February 24, 2009 at 2:48 pm

  19. [...] Eyes Aren’t blog comments wonderful things? Today, I learned from a comment by Nicola on Visualising Financial Data In a Google Spreadsheet Motion Chart that Many Eyes can now be used to visualise live data via Many Eyes [...]

  20. In the formula GoogleFinance(”IBM”, “price”, “1/1/2008″, “10/10/2008″, “WEEKLY”) is an ERORR there should be semicolon beetwean the arguments not a coma

    Jack

    March 17, 2009 at 11:05 am


Comments are closed.

Follow

Get every new post delivered to your Inbox.

Join 768 other followers

%d bloggers like this: