Greenturtle Excel Addins

The addin uses WinHttp to access the data from Yahoo.  The way Microsoft designed that library is to have a separate proxy setting to the one used by Internet Explorer.  You can find more details here if you’re trying to use the real time addin from behind a proxy:

• Using the WinHttp Proxy Configuration Tool

The Greenturtle Excel Toolkit (GTXT) is a set of functions for Excel that will make sheet development easier and cleaner.  Often, spreadsheet developers go down the VBA route to achieve something that is simpler and efficient to do via other ways because of familiarity or habit.  The choice of VBA has implications on not only performance but reliability of sheets. It’s worth clarifying here we’re not against VBA, just that there is a better way to do a wide range of tasks that professional sheet developers do without VBA and for little or no additional administrative overhead.

The toolkit aims to provide robust and performant functions spanning a plethora of areas to enable you to develop sheets like a pro.  These areas will eventually include at least the following list:

• mathematical matrix manipulation (det, norm, trace, eigenvalues, regressions, etc)
• range manipulation (intersect, scale, bump, sort, seek, etc.)
• sqlite access (ability to query sqlite databases via SQL, create databases from ranges, etc.)
• xml access (ability to load and manipulate xml documents)
• calendars (sun / moon calculations, handle dates in World calendars like Persian, Chinese, etc.)
• time series analysis (MACD, oscillators etc.)
• basic financial calculations (day-counts, holidays, period math, etc.)

Overlap with existing Excel functions will be minimized and the functions in the toolkit will use some novel techniques and provide a rich user experience.

The first version toolkit will be released sometime in early May.

The addin allows you to simulate realtime feeds via flat files.  You tell the addin where those files are via the XML config e.g.:

<source key="rates" type="file" source="w:\test.csv"
             lineSeperator="nl" split="," keyToken="0"/>

This sample has been commented out from the default config file supplied.  It tells the addin there is a file ‘test.csv’ file at w:\.  Its record seperator is a nl (newline) and its field seperator is ‘,’ and the key token is the first column in the file.

That file may look something like this:

on,0.03
1d,0.0213
1w,0.0312
1m,0.0421
2m,0.0435
3m,0.0456
6m,0.0512

From Excel you can now use the formula:

=RTD(”GTRtd.RTD”,,”rates::3m::1) to get the value of the column 1 with the ‘3m’ key record. As you update the test.csv file outside of Excel you’ll see the values change in Excel.  So you can for example generate the file periodically from another process and simulate a cheap pub-sub environment using Excel as the client.

Just released version 0.5 of the Yahoo! realtime addin. This version adds two ‘meta’ functions; one to control updates and the other to get the update count, e.g.:

• RTD(”GTRtd.RTD”,,”meta::<src>::updatecount”) returns the number of updates for <src>
• RTD(”GTRtd.RTD”,,”meta::<src>::update=1) will restart updates for <src>
• RTD(”GTRtd.RTD”,,”meta::<src>::update=0) will stop updates for <src>

With these functions you can freeze and unfreeze updates (after certain update counts if you want).

Get the update from the main site (www.greenturtle.us).

Release 0.4 has been put on the main website (www.greenturtle.us).  Fix to overcome Yahoo!s limit of ~200 symbols per request.  Please download and reinstall.  No uninstall needed.

Just released v0.3 of the RTD server.  It fixes a minor problem with getting fields with commas in them.  Also attached the ExampleMarketWatch sheet to the installation.  Please down load from the main page, (www.greenturtle.us), close Excel and simply reinstall.

The config file is located in C:\Documents and Settings\<USER>\Application Data\Greenturtle.  Replace <USER> with your user ID.  It may be different based on the version of Windows you’re running.  The file is called gt-config.xml.

Update: On Windows Vista the file is probably located here: C:\Users\Home\AppData\Roaming\Greenturtle.

The attached sheet allows you to view World indices and enter up to 20 symbols to track live prices on.  You can use this as a basis for a portfolio tracking or watch list sheet.  The sheet also demonstrates the use of conditional formatting to show price movements in colour.  The bold blue cells are user editable, so feel free to play with the tickers or drop down menus as needed in the cells.  The work happens in the ‘Quotes’ worksheet which has the call to the RTD server.  The ‘Watch’ sheet displays the data in a clean and friendly manner.  See screenshot below:

MW Screen

Now included as part of the installation for the RTD Server.

Just posted an update.  Un-install the addin (from the Greenturtle Start menu folder), download the addin again and reinstall.  That will fix the example sheet.  The reason why it was failing was because the config still had a badly formed entry for a realtime file source.

Just released the real time quote addin.  It fetches data from Yahoo! and is implemented as a RTD server.  Download it from the main page (www.greenturtle.us).  It is highly configurable and also allows you to simulate cheap real time feeds via flat files.  This is a good feature for students or classrooms.  The addin configuration allows you to:

• change the Yahoo! base URL for data
• change how fast in seconds update occur
• change the field names in Excel for the Yahoo fields
• choose the line separator, field separator and the key field for file feeds
• setup multiple file sources and Yahoo! sources (for example one for Yahoo US and one for Europe)

The addin is used via the RTD function in Excel e.g.:

=RTD(”GTRtd.RTD”,,”yahoo::goog::bid-rt”)

will return the live bid price for Google.

Here is a screenshot:

Realtime Screenshot