This posting is about the creation of my first data sheet. I'm thinking to keep things organized that I should create a folder for all of these things, so that they're all in one place.

Creating the folder was pretty easy, as all I had to do was to click on Create and specify "Collection". The following screen shot illustrates this:

I created a Data Sheets collection or folder, so that's where I'll be putting all my worksheets that access data from various sources.

Going to start simple, so the first thing that I am going to do is to create a data sheet that provides access to the GoogleFinance functions. I realize this is very easy to do within a sheet, but doing it once and sharing it with my workbooks should save me time in the future.

The syntax for accessing the GoogleFinance functions is quite simple for stocks: =GoogleFinance("symbol"; "attribute");

So the key is you need to know the attributes, and the symbols that you can use. The list of attributes is available here:

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

There is also a list of attributes available for historical data, and for funds. So for now, my data sheet will be focused on providing access to just equity related attributes.

I cut and pasted each of the attributes into the data worksheet, as well as the description for the item. I have also included a label column for both the attribute and description so as to be able to provide a localized label to the consuming workbook given language as an input.

To do this, I've included a GoogleTranslate function, that takes the text to be translated, from language, and to language as an input. Trying to figure out what should be used as the "to" and "from" parameter, and the help text tells me I need a valid ISO-639 language code. I found a list of them here:

http://en.wikipedia.org/wiki/List_of_ISO_639-1_codes

that appear to work. In practice I'm going to assume that cell d1 accepts a valid language code, so validation will need to be done prior to linking this data sheet to another workbook. I've also hard coded the English labels for some of the attributes as code values like "PE" and "EPS" did not translate to different languages.

To make cell referencing easier in the workbooks I create in the future, I defined names for each of the various cells. This way when accessing this worksheet from another, you can reference the name of the cell rather than the row/column. Point being referencing "price" is much cleaner and more intuitive than referencing "c4". The following screen shot illustrates how this was done. The UI was a bit painful, and will have to evaluate whether this is worth the pain for large data sets, but ...we'll see.

In hindsight, not sure how much if any time this will save me, as it is just as easy to define the function as it is to reference a cell, but oh well. In the long run it may give me a consistent interface with the other Data sheets that I plan to create.

Anyways, 30 minutes or so later, and we're all done. I've saved this worksheet in my Data Sheets collection, and it is available here:

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

I was hoping to share / embed this into the blog, but couldn't figure out how to do it. The documentation I saw didn't jive with the options I was given from within the workbook.