Building a Real-Time Data server for Excel, part 1
In this series of posts I will describes the techniques necessary to build a Real Time Data (RTD) server for Microsoft Excel. It is based on what I learned whilst coding GeodesiX, an Excel addin that uses the GoogleMaps APIs to provide Excel formulas to fulfil these functions:
- Geocode. Given the name of a place of interest or an address, find the latitude, longitude, city, country, etc.
- Reverse Geocode. Given a Latitude and Longitude, find nearby addresses, cities, country, etc.
- Great Circle Distance. Given 2 latitude+longitude pairs, find the Great Circle Distance (as the crow flies) between them.
- Travel. Given 2 places, find the travel distance and duration between them, for a given mode of transport (Driving, Bicycling or Walking).
- Display. For all of the above, display the results using GoogleMaps in an embedded Excel Task Pane.
I have open-sourced GeodesiX on SourceForge, you can download the code here.
Audience
Programmers who want to implement an Excel RTD server, for example in financial services. This is not a beginner’s guide, it is assumed that you are totally familiar with Visual Studio, multi-threaded programming in VB, HTML, etc.
Pre-requisites
- Visual Studio 2010, any version (Express is fine)
- Add-in Express 2010 for Microsoft Office and .net
Contents
Finding your way around
AFAIK there is no way to provide an HREF into source code. In order to allow you to quickly find relevant pieces of code, I’ve added numbers in # signs in comments in the code. So if you see #27# in this documentation, you can jump to the relevant code by searching for #27# in the entire solution like this:
Topics
Here is the list of topics that are coming. I am going to publish the first one “Avoiding VSTO” tomorrow. Once a corresponding post is published, I will add a link in the list below.
- Avoiding VSTO
- How RTD servers work
- Architecture
- Excel, Multithreading and callbacks
- Providing easy-to-read function names
- Talking to the GoogleMaps APIs
- Avoiding Application Domain misery
- Embedding a GoogleMap page in an Excel Task Pane
- Creating the Setup project
- Changing the Excel RTD Throttle Interval
- Utility functions
- Building help from the source with Sandcastle