Jackson Miller

I love Microsoft Excel. Unfortunately Excel on the Mac is not as easy to work with as Excel on the PC. One major difference is the lack of ODBC connections without third party software. This has caused me to look to web-based spreadsheets like Google Docs, but unfortunately those leave me wanting as well.

Here is the business problem:

I need to create a spreadsheet that allows multiple people in various locations to see and play with the same data set (in this case the sales information for our retail stores).

Using Excel the data become stale. We have to either manually update the information on a regular basis or we have to regularly run exports and import CSV files. Both scenarios create a situation where the spreadsheet is out of date when we need to answer a question or curiosity and it takes too much work to update it.

We started using Google Docs as an alternative. I wrote some custom scripts that updated the Google Doc each night. It is a delicate process though and it is easy for the data to get out of whack. Also, there are some serious limitations to the number of calculations you can have in a Google Spreadsheet. This meant that we had to limit the amount of information (i.e. detail and accuracy) that we stored in the spreadsheets. Also, Google Spreadsheets are slow (especially if you are pushing the limits). After a while the spreadsheets became unreliable.

Enter Google App engine.

Since I have become a big fan of Google App engine I decided that it would be better to use a BigTable data store in AppEngine. This eliminated the restrictions on the level of detail and accuracy that we could store within our data set. It also reduced the propensity for errors in the updating process. The new problem was then how to put that data into a spreadsheet that could be shared and manipulated by the stake holders.

Google Spreadsheets has a function that allows you to pull in a web-based CSV into the spreadsheet: ImportCSV(url). I was able to create a set of CSVs on the AppEngine account that pulled out the desired information and placed it in the spreadsheet. Unfortunately, the Google Doc was still slow and did a poor job of providing feedback that the information was being updated. If only there was a way to pull web-based data into an Excel Spreadsheet. (This would have been easy to do with ODBC, but we are all on Macs.)

Turns out, there is a way. It is not exactly documented and publicized. There isn’t really a provided user interface, but it can be done.

The end result is an Excel spreadsheet that updates the data every time it is opened. I am still storing the data in Google AppEngine (as a free web-based data store), but the actual source of information could come from any system. Also, once everything is setup the spreadsheet can be emailed to new people and the data will still update without them having to do any setup on their side. I am even able to create calculated columns from the remote data and have them update automatically. So far, this seems like the best solution for our problem.

In my next post I will detail how you can create a web-enabled spreadsheet too.

I have been looking for office space for a few weeks. My requirements are simple: a window, walking distance to coffee, snacks, and/or lunch, 24 hour access, secure, and in East Nashville. I also have a desire for the space to be the kind of place that I want to go to every day. These are simple requirements, and if I was willing to go across the river I would have a plethora of spaces to choose from.

The spaces I have been finding are all over the map. There are run down offices, offices that were apartments that were offices, small houses in residential areas, and brand new Grade A space that no one is renting. The best part is that the prices are all over the map.

When Joel Spolsky talks about office space, developers listen. He has now outgrown his current space and is in the process of selecting their new space. It is nice to read about Joel’s decision making process. I don’t have many of the same worries as him, but I am having the “nice” vs “cheap” debate with myself. I think I am leaning towards “nice”. The question for me is, how much will “nice” help my productivity. I think the benefit outweighs the cost, but I have to get solid figures on cost before I can make the decision. Hopefully I can get it squared away this week.

That title is a pretty bold statement.

I could write a long post about how document sharing, collaborative editing, integration with Google Gadgets, etc are all game changing features. The thing is, that post would take too long to write and I don’t have that time right now. Luckily I can back up my claim with a simple formula that is available in Google Spreadsheets and next to impossible in Microsoft Excel:

=GoogleFinance("RTL","close","4/25/08")

Did you get that? Do you see what is going on there?

Google is pulling historical information from the finance markets for the ticker symbol that you specify. That example will pull Friday’s closing value for RTL (the retail market index). That specific example is significant to me because it will allow me to track the sales trends of our retail stores against the market average. That is huge.

I am sure there are some uses for using non-index ticker symbols too, but for me the ability to measure against index funds is a killer feature. I think it is such a killer feature (coupled with the stuff I mentioned earlier and remote scriptability) that I am less likely to upgrade to Microsoft Office 2008 for Mac. The only reason I will upgrade is to maintain ubiquitous compatibility with the masses.

Oh, and did I mention that they are working on offline access to Google Spreadsheets?

I was just checking in on Office 2008 for Mac and noticed that Microsoft has a “Super Suite Deal” going on. If you buy any version of Office 2004 for Mac, they will give you a free version of Office 2008 for Mac when it is released. Here is the details:

“When you buy Microsoft Office 2004 for Mac Standard Edition, Office 2004 for Mac Standard Edition Upgrade, or Office 2004 for Mac Student and Teacher Edition, we’ll send you Office 2008 for Mac Special Media Edition (a $500 value) for FREE* – you only pay shipping and handling.”

That is a pretty good deal if you ask me. I think I am going to have to buy Office 2004 for Mac. I just wish I could get my hands on a beta of Office 2008.

Update: I just noitced the Office 2008 for Mac team has a blog. Sweet.