Saturday, November 24, 2012

Plot Custom Feed in Google Finance

Big fail on Google's part for making it so difficult, but it is possible. -- sometimes

Let me back up a bit. Google Finance provides stock charts. You can even create a portfolio, albiet through a clumsy interface. What's good about the portfolio is that it automatically tracks dividend payments and current values and gives you a good overall picture of how you're doing. What's bad is that there is no good way to decorate a chart to show when you bought and sold.

Google explains how to plot feeds, but that article is hoplessly out of date. Here's what you actually have to do.

If you search for a company, or browse to them from your portfolio, the url displays a lookup by stock symbol. For example: http://www.google.ca/finance?q=CVE%3AGBN

The default view of a chart shows a bunch of news flags. To remove them, click the "Settings" link at the bottom left of the chart and uncheck the "News Flags" box. Next click the "Plot Feeds" link also at the bottom left of the chart. In the text box that appears, enter this sample url http://spreadsheets.google.com/pub?key=pGtHppkjvbFk80vSJte23Zw from Google's how to plot feeds guide and click the "Plot Feeds" button. It appears that nothing happens, but that's just because the sample data is from 2008. Click the "All" link at the top of the chart to display the full time range 2001-2012, and you should now see flags A-G.

Now go into Google Drive and create your own spreadsheet. I laid mine out exactly like theirs with the same headers and columns, but this one contains my buy data. I changed sharing from private to "anyone with the link can view", although I don't think that's necessary. What is required is: File > Publish To The Web > Publish Now. And you have to copy the published link they show you. Here's mine: https://docs.google.com/spreadsheet/pub?key=0Ao0U_IQ5WktpdF90dWVPYk9LVkdJaUdxZXkzeU5Ubmc&output=html

Now, back to the stock chart http://www.google.ca/finance?q=CVE%3AGBN, and click the "Clear Flags" button to remove the data from Google's sample spreadsheet. Then paste in your published url and click "Plot Feed". This displays the following error.

The feed address you entered has returned no data.

Googling that shows that everyone has this same problem. It seems to be be related to the fact that the working example is served from spreadsheets.google.com. I re-posted the source of the working example on my site and tried it from there and got the same error, so it seems that Google Finance rejects feeds from any domain other than spreadsheets.google.com.

But here's the trick. Your published spreadsheet is actually available from spreadsheets.google.com, you just have to craft the url manually as follows.

https://docs.google.com/spreadsheet/pub?key=0Ao0U_IQ5WktpdF90dWVPYk9LVkdJaUdxZXkzeU5Ubmc&output=html

http://spreadsheets.google.com/pub?key=0Ao0U_IQ5WktpdF90dWVPYk9LVkdJaUdxZXkzeU5Ubmc

Now try your spreadsheets.google.com url in the "Plot Feeds" text box and click the "Plot Feed" button. Success!

Note: shortly after I published this article, I tweaked my spreadsheet a bit and the plot feeds stopped working. No error is displayed, but no flags appear. I re-created several test spreadsheets from scratch. I'm not sure what invalidates them, but it seems like it is entering any numeric fields. Here's an example that works.

https://spreadsheets.google.com/pub?key=0Ao0U_IQ5WktpdGw3VncxdHp4X2ljQmItVVZEVS14T1E

The above was created by the following operations. New spreadsheet. Delete columns after C. Delete rows after 20. Drag header-bar down one row. Define headings: Date, Action, Price. Enter data (note the text that preceeds the price to prevent it from being numeric). Select all choose center alignment. Close spreadsheet and rename from untitled. Open spreadsheet. File > Publish to the web > Start publishing.

{ "loggedin": false, "owner": false, "avatar": "", "render": "nothing", "trackingID": "UA-36983794-1", "description": "", "page": { "blogIds": [ 342 ] }, "domain": "holtstrom.com", "base": "\/michael", "url": "https:\/\/holtstrom.com\/michael\/", "frameworkFiles": "https:\/\/holtstrom.com\/michael\/_framework\/_files.4\/", "commonFiles": "https:\/\/holtstrom.com\/michael\/_common\/_files.3\/", "mediaFiles": "https:\/\/holtstrom.com\/michael\/media\/_files.3\/", "tmdbUrl": "http:\/\/www.themoviedb.org\/", "tmdbPoster": "http:\/\/image.tmdb.org\/t\/p\/w342" }