Monthly Archives: July 2013

xllutility

I’ve updated https://xllutility.codeplex.com. These are tools I use to test my spreadsheets and diagnose problems. I use them so often I forget not everyone knows about this.

Had a client recently in denial about a third party add-in. HUEY.RUN and Task Manager got the finger pointed away from me. The great thing about computers is that they do exactly what you tell them to do. The awful thing about computers is that they do exactly what you tell them to do.

Advertisements

The Eighth Wonder of the World.

Okay. Maybe I’m exaggerating. I’m the last guy in the world to cast aspersions or proselytize when it comes to programming languages. I spent three years at Morgan Stanley writing A+ code and still think that was the most expressive and productive language I’ve ever used. I even wrote a GUI in it, and I have no idea how to write GUI’s.

They had a program called COPS for doing Monte Carlo simulations. Nobody knew what the acronym stood for and the guy that wrote it went over to the group this book was written about. Another example of somebody sitting down in the wrong seat at the right time, getting fired, and trying to capitalize by writing a book. Publishers are great at figuring out how to manipulate these people.

LSS, I used the NYC police car white on blue colors for the app, and got some cred with the A+ gurus at MS for figuring out how to get the police stripe to resize correctly. The first time I showed it to traders they told me to change the color scheme to black background with shades of grey text. They had to look at it all day.

I’m a unix guy, born and bred, and thought gcc and vi were ideal tools for writing code. A+ was a shock to me. No need to write a clumsy command line interface, just start up the interpreter and you can look at anything you want. Debugging consisted of setting a break point. Then you just use the language to poke around. Kenneth Iverson was way ahead of his time. Kdb is a legacy of that.

You can see many of those ideas in my http://tukhi.com/ product. It is a spin off of a product I’ve been developing over the years for my hedge fund clients. The key to performance in Excel is array formulas. Especially the FP data type, something you can only get at through the old school C SDK.

One consistent complaint is that Excel is too slow. I have to rely on Excel recalculation to provide the fodder for collecting statistics over a simulation. Users can enter any formula and use 3rd party add-ins I have no control over.

If they get 1000 recalculations/second they seem happy, 100 and they suffer in silence, but when it gets down to 10, or even 1, that is their pain point.

Last week I whacked something together to find the upper bound on what Excel is capable of. My clients need millions of data points, more than can be fit into even the big grid, and I’ve been using databases for that. ODBC is more performant than you might think, but I was looking for more.

I’ve looked into Hadoop, MongoDB, and other Big Data “solutions.” 1010data has a product far ahead of either of these. Those guys seem to be on the right track. Forget the BI stack, just put data in rows and make typical queries so fast you don’t need a big IT team to make that happen.

I wrote 200 lines of code to create a file of doubles, memory map it in, and iterate over the doubles one at a time. Don’t get my client’s hope up, but I clocked it at 60,000 recalcs/sec on my Lenovo X220 laptop.

You can see how many lines of code I was able to reduce that to: http://xllcdb.codeplex.com/SourceControl/latest#trunk/xllcdb.h

This is entirely due to the expressive power of the latest C++ standard. Just like Java taught us how productive garbage collected languages with off-the-shelf libraries can be, the latest C++ standard provides off-the-shelf means to write code that makes it possible to get the highest possible performance out of the silicon using only a few lines of code. It is just a matter of learning how to leverage off of what some of the smartest people in the world spent considerable time and effort producing.

Add-in Manager

You can always load an xll using File>Open... which then calls xlAutoOpen, but Excel has a mechanism for loading an xll every time you run it. In Excel 2003 and earlier Tools>Add-Ins... will get you to the Add-in Manager dialog. In 2007 and later File>Options, then Add-Ins and Manage: Excel Add-ins and pressing Go... brings it up.

You can Browse... for the location of your add-in and when you click OK
xlAutoAdd then xlAutoOpen are called. Deselecting an add-in calls xlAutoRemove then xlAutoClose.

The xll library provides hooks for calling any code you please from any of the xlAutoXXX events. Just write a function, e.g., int xll_foo(void) that returns 1 on success and 0 if things go pear shaped then create a static Auto<XXX> xao_foo(xll_foo) object and Robert is your father’s brother. Multiple instantiations add functions to the list.

The xll library implementation calls a function to register all the AddIn objects in xlAutoOpen and Auto<Open> functions get executed before that. If you need to call something after the functions are registered you can use Auto<OpenAfter>. Likewise, xlAutoRemove unregisters the AddIn objects. If you need call something before the functions are unregistered you can use Auto<RemoveBefore>.

The gory details can be found in: auto.cpp.