These documents 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. It is released under the Simplified Free BSD License.
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.; if you don’t understand something, please Google it, or better still look on StackOverflow
- Visual Studio 2010 or later, any version (Express is fine)
- Add-in Express™ 2010 for Microsoft® Office and .net (“Avoiding VSTO” below explains why)
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:
I suggest you read these in order. There is a NEXT link at the foot of each page.
- Avoiding VSTO
- How RTD servers work
- 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