Monday, February 13, 2012

Company Overview - Google Docs version



Previously I spent some time using the SMF Addin to create a "Company Overview" workbook. This workbook pulls in financial data, time series (price history) data, and news headlines from Yahoo Finance and is available here:

http://www.toteboard.net/ - then there is a link on the right hand side for the Excel version of the Company Overview

Given my new focus working with Google Docs Spreadsheet, I figured I'd see if it was possible, and if so, how difficult it would be to replicate this workbook from Google Docs. The following are the steps that I went through to accomplish this.

Step 1 - Replicate the Data sheet.

As part of the Company Overview workbook, I leverage a "Data" worksheet as a mechanism for pulling in data from Yahoo Finance. In my Excel version, I leverage a function defined in the SMF Addin (RCHGetYahooQuotes)to pull in CSV data from Yahoo Finance. Given a series of paramters, this function makes a call to download.finance.yahoo.com to pull in financial data for a specific company. For example, the following URL is leveraged in this sheet:

http://download.finance.yahoo.com/d/quotes.csv?s=EFA&f=snll1cc1t7va2bapomwj5j6k4k5ers7r1qdyj1t8e7e8e9r6r7r5b4p6p5j4m3m7m8m4m5m6k1b3b2k2c6c8&e=.csv

Take a look here to get a definition of the fields that are provided thru this function:


The nice part about Google docs is that you can leverage the ImportData function to pull in this data. So, no custom function is required to pull in csv data. To pull this data into the data sheet, I've concatenated the base url, the symbol for the company, and the field list together in cell c4. I then use the concatenated values from cell c4 as the input parameter to the ImportData function in cell h8. I put it there to replicate that same starting position as in my Excel version. The result of my function is a list of data items beginning in cell h8 going thru cell BE8. I then cut and pasted additional calculations into cells BF8 thru BN8. (NOTE: Some of these functions that worked in Excel threw errors in Google Docs as numbers coming from yahoo finance as say "+5.15" were treated as strings, while in Excel they were correctly treated as numbers.) Values like -4.53 were treated correctly as numbers. To address this issue, I had to use the following: value(substitute(M8,"+","")). This simple function replaced the + sign with a null string, and then treated the resulting set as a numeric value. I had to do this for any function that could return a positive number.

The next set of data that I had to move over were the calls to get the historical prices so that I could create a price chart. To get pricing data, the following url is called from Yahoo Finance:

http://ichart.finance.yahoo.com/table.csv?s=EFA&g=d&a=10&b=5&c=2008&ignore=.csv

To simplify this, I just hard coded the start date, but I build the url give the symbol currently being selected (this is done in the Data sheet in cell H36). Again, the ImportData function is used in starting in cell H38 to pull in this data. I then do simple cell referencing in columns O and P to simplify the charting of the data.

The final data set that we need to pull in is a list of headlines for the selected company. In my Excel sheet I leveraged the following url:

http://download.finance.yahoo.com/dnh?s=DUK&n=20

But in the Google Docs version I switched this over to leverage an RSS feed from Yahoo. The url for this is defined in cell B35 of the Data worksheet. Example as follows:

http://feeds.finance.yahoo.com/rss/2.0/headline?s=EFA&region=US&lang=en-US

In this case, the ImportFeed() function was leveraged in cell B38 of the data sheet using the above url as the input parameter.

Step 2 - The Setup worksheet

One of the things I like to do, on top of having a worksheet dedicated to data, is to have a setup worksheet. The intention here is to provide a place where the user can go to "setup" their specific options for the workbook. In my Excel version of this solution there was not a whole lot there, but in the Google docs version I wanted to provide the user with the ability to see the relevant data and labels in the language of their choice. To accomplish this I defined each of the labels that I was going to use in the "Company Overview" worksheet in column B. So, these were the English abbreviated versions that I wanted to display. I then spelled out each of this labels in column C, with the intention of using these as an input to the GoogleTranslate() function. Column D is where I leverage the GoogleTranslate() function. Input to these calls are the labels defined in column C, and then the language as specified by the user in cell C2. The resulting translations were then returned in Column D. I then have simple function in column E that uses my abbreviated labels from Column B if the language is in English, else it uses the translated values from Column D. The value here (as you will see on the Company Overview sheet) is that the user can get the labels in the language of their choice. Now, I have no idea how good the translations are, but if they are close, I am hoping that this will be good enough.

Step 3 - Build the display worksheet

The final step is to build out the display of the data on the Company Overview sheet. Now this differs a bit from my Excel version, but the concept is the same. The difference is that I leverage the Setup sheet to get the translated labels for the display. The actual data is referenced in exactly the same way. (Note: To speed the building of the Google Docs version I tried to cut and paste the functions from my Excel workbook. I had a problem though when trying to cut and paste multiple cells at a time as the cell referencing got messed up. To work around this I cut and pasted a single cell at a time.) For display purposes, I repeated the steps to remove the "+" sign from postive numbers.

For the Headlines section I leveraged the Hyperlink function to allow the user the ability to click through to the story matching the headline.

For charting of the pricing I leveraged the Interactive Time Series gadget. One problem that I found though was that when trying to print the worksheet, this gadget anyways was not printed. As a result, I created a simple time series chart and placed it behind this gadget to support the printing use case.

That's about it.....all told it took maybe 3-4 hours to move this over. Good news is that I probably was able to carry over 90% +/- of the functionality that I had in my Excel version. Then, on top of that I was able to add in the ability to provide a localized solution on the fly. So, if English isn't your first language, via the Setup worksheet you can change the default text language on the fly. (Note: I could probably get closer to 100%, but in the end, it was good enough.)

The end result in Chinese is displayed below:


You can sample this spreadsheet at:

https://docs.google.com/spreadsheet/ccc?key=0Aj8CeiznlFexdGctenNmdlctSGpxNUdZLWk5OUlZV3c


or you can leverage the Company Overview Template here:


Finally given what I experienced, if I could......, I'd make the following suggestions to the Google team to improve the spreadsheet application of Google Docs:

1. Fix the cutting and pasting of formulas from Excel to Google docs.
2. I tried importing my Excel document, but this failed, so I had to do it worksheet by worksheet, and then cell by cell.
3. Provide the ability to protect an entire sheet, except for a specific cell. Ideally the template would be entirely protected, and all the user would have the ability to change would be the symbol they type in to cell c2, and the language they can select in the Setup sheet.
4. Provide for the ability to hide column and row headings. You can hide everything else, so why require that you always see A, B, C, .... and 1, 2, ,3 etc. Without this and with everything else hidden you may not know that this is a spreadsheet template.
5. I had problems when trying to pull in 10 years worth of price history data. The spreadsheet stated that it was "working" but it never seemed to finish, although shutting down and restarting the browser, and coming back to the workbook everything seemed to be saved correctly.
6. It'd be nice if there were a series of UI controls like combo boxes or list boxes that you could use. The data validation function that I used for the language is a close approximation to a list box, but other controls would be extremely useful and would provide for a much better UI.
7. Gadgets should be printable if at all possible.



4 comments:

  1. JP, I saw your post in the SMF Add-in Group discussion. Thanks so much for sharing this spreadsheet and your thought process, great stuff!

    ReplyDelete
  2. Good morning, I econtrado your excel sheet which seems very good but when I type, for example, SAN.MC, which belongs to the Spanish market gives me error, however when I type CNA.L comes out perfect. I could help?

    thanks

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. Hi Vimasair, I just had a look and it seems that not even Yahoo supports SAN.MC properly, hence that the spreadsheet is failing.

    I think American and London markets are properly supported, but the rest not quite.

    ReplyDelete