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.

One more time.....Google Docs Spreadsheet

OK, it's been about two years since my last post, so looking to add these things a bit more often now. This time around though, for say the next year or so, I'm going to focus on just using Google Docs Spreadsheet. (Step 1 for Google is to come up with a catchier name.) I've always said that Microsoft Excel is the "great equalizer" in that it allows us semi capable technologists the ability to create tools / applications that come close to rivaling applications created by the super geeks. It also gives others the ability to "tweak" it to their liking. Something that is not possible with online solutions. Question now is whether Google Docs Spreadsheet is ready for the prime time.

Anyways, having switched to Google Chrome nearly two years ago (never once regretting it), figure it is time to cut the cord and see if Google Docs Spreadsheet is any where near Microsoft Excel. I'm guessing no, but key question then will be is it good enough, and if not what needs to change so that it is. Again, what interests me are the gadgets (that can be shared onto iGoogle), on the fly translations, the data retrieval tools, and the online nature of the tool. As in, being able to provide access to my spreadsheets from virtually any device opens up a huge user base. Yes Microsoft has an online version of Excel, but it's pointless if you can't leverage VBA. (I don't think you can, but someone correct me if I am wrong.)

In looking at the spreadsheets I have done in the past, you can break each of them down into 3 parts:

- The visuals / display

- Calculation / business logic

- Data retrieval

As I start my journey I'm thinking that I'll step back and try and do this the right way this time. So, I'm going to focus on a few things out of the gate. Specifically, I'll be defining and creating the visual experience that I want (think CSS in html terms) in terms of a series of front end or visual templates for my workbooks. So, defining color schemes, fonts, screen layouts, etc.

On top of that, and probably more importantly, I'm going to focus on creating a series of "data" worksheets that can be used / accessed to provide access to various data sets.

Finally, I'll determine whether I need to define a separate set of calculation worksheets that manipulate data from the data sheets for display usage by the visual sheets.

In general, experience has taught me.....focus on getting the data, doing any required calculations and only then focus on the visual experience for the user. So, step 1 is to create a series of data sheets that I plan on using. As my interests lie in fantasy sports (especially baseball) and investing, my first plan of attack is to create a series of data sheets that provide access to that content.