Stuff you’ll need to know when coding an Excel Add In for a trader in C++

Excel Add Ins are a special kind of DLL – an XLL – that exports certain function signatures that Excel expects to find. For example xlAutoOpen, xlAutoClose and xlAddInManagerInfo. Coding an XLL means you can implement worksheet functions and commands in C++. Worksheet functions can be called from Excel formulae, and commands can be invoked from menu items you can add to the Excel menubar in initialisation code. To get to grips with all this you need…

  • The MS Excel 97 SDK – get it here. Download Frmwrk32.exe. Essentially the SDK is two files: xlcall.h and xlcall32.lib.
  • You need a copy of Steve Dalton’s Excel add-in development in C/C++. Don’t skimp on spending the 40 quid, you do really need this book.
  • Another MS knowledgebase article giving a simple example of an XLL. This will give you some boilerplate code to get started.

If you want to get real time data into your worksheet, you’ll have to master the mysteries of DDE, or else go the Excel RTD route. I haven’t done RTD, so won’t comment here. I have done DDE. Here’s a useful starting point.

Why do we need DDE ? Because you can only do this…

Excel4( xlSet, &xRet, 2, (LPXLOPER)&xRef, (LPXLOPER)&xValue);

…from Excel’s own main thread. That is, you can only make that call when Excel has called you, and you’re in the stack context of a worksheet function you have supplied. When you’re pushing real time data into a worksheet, you’re typically getting a callback on another thread from your pub/sub messaging API – TIB RV or whatever. You can’t call the Excel API from that thread, so you use DDE to put a Windows message on Excel’s message queue, which will hopefully be picked up quite soon by Excel’s main event loop. There’s no reason why you can’t have more than one thread operating inside your Excel process, executing the code in your add in. So long as your code is threadsafe, of course ! So your message handling callback will make the DDE call to post to Excel’s main thread.

Bearing that in mind, there are some guidelines you should follow…

  • Always remember that Excel is effectively single threaded.
  • You should only have one real time data source per Excel process. For a pricing sheet, that might mean only one futures contract. Don’t attempt to have two sheets driven by two futures contracts in the same Excel instance: Excel’s recalcs will be slower.
  • If your sheet does a lot of calcs in response to an incoming tick, you’ll need a throttling mechanism that’s aware of your calc cycle. Otherwise you may find yourself starting a new calc cycle before the last one ended !
  • And look out for string pooling. If you’re doing a release build with /GF, you may get the “not a valid add-in” message box. This KB article desribes the issue and workaround.

When you’ve got your addin and pricing or risk sheet running, and you want to serverize it, take a look at SpreadServe.

 

Some other useful content

13 Responses to “Excel”


  1. […] Excel 12 aka Excel 2007 is going to be a big one for front office developers. Having coded XLLs for real time pricing and autoquoting, I was wondering when Excel would support multi threaded calcs. The good news is that Excel 12 adds multi threaded execution of XLL worksheet functions. This means that pricing sheets will now be able to fully exploit multi processor hosts. […]

  2. felix Says:

    i was wondering how you are using DDE with an XLL…
    do you have a thread created by the XLL the issues the DDE calls for incoming data?

  3. anonXLguy Says:

    Always use RTD – never use DDE. You won’t have to worry about calc cycles — although you should still always monitor calc time programatically and throttle updates accordingly, or else take control completely with manual calc. But calc won’t crash you with RTD (unlike DDE)

  4. ted Says:

    does anyone know how to turn off multi-threading in Excel 2007? A lot of the code I inherited is not threadsafe and this causes serious problems going from Excel 2003 to 2007.

  5. Phil Says:

    see http://msdn2.microsoft.com/en-us/library/aa730921.aspx
    search for “Controlling Calculation Options” in there. Or Choose Excel Options, Advanced, then Formula tab and finally uncheck “Enable multi-threaded calculation”. I haven’t checked automation for this on how to do it programmatically.

  6. RaffC Says:

    Hi,

    In your text above you say;

    “DDE to put a Windows message on Excel’s message queue, which will hopefully be picked up quite soon by Excel’s main event loop”

    How do you do this exactly? Do you use WM_ type messages? I have a DDE proxy and would like to perform something similar in an Excel Add-in.

    Any advice would be extremely grateful.

    Thanks in advance,
    RaffC

  7. AlexesDad Says:

    XLW makes writing C++ & C# xlls VERY easy as demonstrated here :

    Creating a C++ XLL with Visual Studio

    Creating a C# XLL with Visual Studio

    Creating a hybrid C++/C# XLL with Visual Studio (Professional)

    XLW (xlw-4.0.0b0) can be downloaded from
    https://sourceforge.net/project/showfiles.php?group_id=45222&package_id=37893&release_id=690866

  8. Raffc Says:

    Hi,

    I am slowly getting upto speed coding my xll addin (based on Daltons book). I have a function returning an array into Excel. Now however, I would like to use xlSet to populate the cells.

    I copied a simple example but when I use it, it leaves the cells blank? I looked in Daltons book and not much is clear to me what is wrong. Can someone please state the obvious?

    in Excel cell A1 I type =test2(23)

    __declspec(dllexport) short WINAPI test2(short iVal){

    static XLOPER12 xRef, xValue;
    static XCHAR rgch[32];

    xRef.xltype = xltypeSRef;
    xRef.val.sref.count = 2;
    xRef.val.sref.ref.rwFirst = 5;
    xRef.val.sref.ref.rwLast = 6;
    xRef.val.sref.ref.colFirst = 2;
    xRef.val.sref.ref.colLast = 2;
    xValue.xltype = xltypeInt;
    xValue.val.w = 22;
    Excel12(xlSet, 0, 2, (LPXLOPER12)&xRef, (LPXLOPER12)&xValue);
    return 1;
    }

    • etrading Says:

      In his documentation for xlSet (p203 8.7.4) Dalton explains that it only works for commands not functions. He explains the difference between commands and functions on p19 in 2.8. You’re invoking xlSet in the context of a function, not a command, which is why it doesn’t work. Nevertheless, the code you give should return the value 1 to the calling cell in the sheet. The fact that it leaves cell A1 blank suggest there may be an error in your call to xlfRegister. I suggest a close reading of Dalton ch6. Also download QuantLibXL and build from source. Read the QL code, and run in the debugger. That will give you a feel for XLL coding.

  9. Raffaele Says:

    Hi,

    Thank you for your reply. I did manage in the end to get it work.

    As you say in Daltons book he mentioned the concept of the xlSet being executed from a command. So I now call xlSet from the xlcOnTime which is invoked from the xlAutoOpen.

    Many thanks!!

  10. Ethan Says:

    Hi admin do you need unlimited content for your site ?
    What if you could copy post from other pages, make it unique and publish on your website
    – i know the right tool for you, just search in google:

    kisamtai’s article tool

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s