All posts by ian

Modular Excel Spreadsheets

Where I work there is no escape from the clutches of Excel. It gets everywhere. Everyone uses it for everything.One of the projects I’ve been responsible for is an attempt to modularise Excel workbooks in order to make them easier to create and manage. This is done by creating a manifest for each “component” (a worksheet, and optionally some VBA code-behind) that expresses the inputs that it consumes, and the outputs it provides. Then it’s simply a matter of recursively creating and joining these components together until we get a complete workbook.It turns out that making the abstraction that a component is nothing more than a set of inputs and outputs, and having the joining framework know nothing about the functionality of each component, means we can easily apply it across a wide range of applications. Generally it’s used by structurers to construct quite complex spreadsheets for valuing derivatives on one or many underlying assets, but it can also be used to construct “trading book” type spreadsheets that download a set of trade data and value them, automatically creating the market data components required to do the valuation.

The framework itself is written as a COM server that lives inside the Excel process, and controls it via automation, although it also works out-of-process. It supports IDispatch, so is usable from scripting languages, too. For instance I’ve put together an HTML/JavaScript based UI that can invoke the framework directly from a web browser.

The main problem is the fact that Excel is the host application. There are lots of strangenesses around the Excel automation API, but it still does a pretty good job, in my opinion, of exposing so much functionality. The real issue is that it’s a black-box. As is the VBA engine (VBE.DLL). This can make it difficult to track down problems, especially where I’m crossing backward and forward between native C++/COM code and VBA callbacks.

Still, this is a satisfying project, because it helps to formalise people’s use of spreadsheets, without them even realising. It’s easier for end-users to drive; they select what type of product they want to value from a simple GUI and the framework does the rest. And from a development point of view it means the analytics group have fewer monolithic and difficult to maintain spreadsheets. And of course, forcing us to think about the model in component terms also means that if we should ever move away from Excel (!) we would, at least in theory, have an easier migration path.

OpD2d Update

I finally got around to building and packaging a new version of OpD2d. Major new features are:

  • Preview window so you can easily check volume levels before recording
  • Ability to pass recording settings on command line so you can use Windows Scheduler to record automatically at a certain time

It’s available here: http://www.opcode.co.uk/opd2d/default.asp

OpD2d has been downloaded about 5000 times a month for the past few years, and I’d like to take this opportunity to thank the people who’ve been generous enough to paypal me a few quid as thanks. This is much appreciated, and helps to keep me in t-shirts.

Boost causes Visual Studio 2005 to crash

After experiencing some nasty crashes in Visual Studio 2005 recently, I’ve discovered that the long, heavily templated type names in version 1.33.1 of the boost headers don’t play well with the internal buffer sizes in various parts of Visual Studio (including SP1). This results in crashes while generating Intellisence data (feacp.dll) and in the debugger itself. It’s fairly easy to reproduce, essentially all you have to do is #include adjacency_list from the graph part of the boost library. I’ve raised a support incident with Microsoft, so we’ll see what they have to say about it.

.NET goes all functional

It’s interesting to watch how C# has developed over the past few years, becoming more and more like a functional programming language, with a declarative style favoured over the imperative approach of previous generations. This is especially obvious in C# 3.0, with LINQ borrowing and building on some technologies directly from the world of functional programming; e.g. lambda expressions, type inference and anonymous types.

It’s especially interesting for me, because where I work there is a lot of discussion of Excel as a functional programming language. For good or bad, Excel is one of the most heavily used “development” platforms used by people in banking and finance. And most of those people don’t think of Excel (and user defined functions within it) as a side-effect-free, functional language, although this is essentially what it is.

Having said that, most of the “real” development work that I do is still in C++, and it was quite a switch moving back even from some of the C# 2.0 I’ve been doing lately, with its anonymous delegates and generics, to what suddenly seemed like an incredibly verbose set-up in the C++ STL collections. It will be interesting to see how the C++0x lambda expression support is implemented, but to be honest I don’t think it’s enough to convince me; there are still too many complexities in C++ for it to be as productive as C#/.NET for the “average” developer.
It’s plain to see that the declarative approach is much more where we want to be as developers in a world of multi-core and many-cpu computing. At that point I really want the language to help me think about my algorithms at a much higher level; and let concurrency concerns be dealt with as automatically as possible, perhaps thanks to restrictions the language has placed on me such as my functions not having any side effects. As anyone who’s written (or studied) complex multi-threading code will know, it’s virtually impossible for human beings to deal with concurrency as a thought exercise. And in fact, even finding bugs in actual implementations is difficult because of the non-deterministic nature of multi-threading bugs.
It will be interesting for me to see how the multi-threaded calculation tree in Excel 2007 impacts overall spreadsheet calculation speed. It looks like the people at Microsoft have taken advantage of some of the functional aspects of Excel (even though they haven’t got as far as some people would like) and married it with a somewhat concurrent implementation. Now all I have to do is rewrite those hundred or so XLLs….

Bug in _com_ptr_t::QueryStdInterfaces

Just thought I’d bring people’s attention to a bug in the COM support classes that ship with Visual Studio 2005/VC8.

It’s a fairly unusual edge case (at least, if you’re not being passed VARIANTs from a script language), where the _variant_t helper class attempts to extract a standard COM interface (IUnknown or IDispatch) from a VARIANT that’s being passed “byref”, e.g. has a type or’ed with VT_BYREF.

In this case the code in comip.h uses VariantCopy to “derefence” the pointer – convert it from, say, VT_DISPATCH|VT_BYREF to VT_DISPATCH – but if this succeeds, it then proceeds to use the wrong local variable, varSrc, rather than varDest that it’s just converted. The effect is that it causes an access violation.

Here’s the code snippet in question:

// Try to extract either IDispatch* or an IUnknown* from

// the VARIANT

//

HRESULT QueryStdInterfaces(const _variant_t& varSrc) throw()

{

if (V_VT(&varSrc) == VT_DISPATCH) {

return _QueryInterface(V_DISPATCH(&varSrc));

}

 

if (V_VT(&varSrc) == VT_UNKNOWN) {

return _QueryInterface(V_UNKNOWN(&varSrc));

}

 

// We have something other than an IUnknown or an IDispatch.

// Can we convert it to either one of these?

// Try IDispatch first

//

VARIANT varDest;

VariantInit(&varDest);

 

HRESULT hr = VariantChangeType(&varDest, const_cast(static_cast<const VARIANT*>(&varSrc)), 0, VT_DISPATCH);

if (SUCCEEDED(hr)) {

hr = _QueryInterface(V_DISPATCH(&varSrc)); // Should be &varDest

}

 

if (hr == E_NOINTERFACE) {

// That failed … so try IUnknown

//

VariantInit(&varDest);

hr = VariantChangeType(&varDest, const_cast(static_cast<const VARIANT*>(&varSrc)), 0, VT_UNKNOWN);

if (SUCCEEDED(hr)) {

hr = _QueryInterface(V_UNKNOWN(&varSrc)); // Should be &varDest

}

}

 

VariantClear(&varDest);

return hr;

}

Looks like it’s the same in Visual Studio 2005 SP1, but Microsoft are aware of the problem, and hopefully there’ll be a patch soon.

Hope this saves you some time!

Symbol loading in Visual Studio 2005 and 2008

I don’t know if I’ve been spoilt by the lazy symbol loading in WinDbg, but it seems incredibly slow to start up unmanaged processes under Visual Studio 2005. It spends a huge amount of time attempting to load symbols for every single DLL that gets loaded. As far as I can tell it doesn’t do any caching or recording of the fact that symbols aren’t available for specific binaries. It just blindly tries every time.

Anyway, I’ve found a way to improve matters slightly. It turns out you can use the symbol server DLL – symsrv.dll – from the most recent version of WinDbg (6.6.3.5 at the last check) to replace the version that ships with Visual Studio 2005.

One of the additional features that the WinDbg version offers in the exclusions list. You can use this to avoid loading symbols for anything other than the DLL under test. Simply copy the DLL into C:\Program Files\Microsoft Visual Studio 8\Common7\IDE and create a symsrv.ini file with the following contents:

[exclusions]
*.*

This should make your symbol loading fly… yet it still loads the symbols required to debug as normal. Magic!

The application failed to start because the application configuration is incorrect

Have you been seeing “The application failed to start because the application configuration is incorrect” errors? I’ve been doing some work on Windows Side-by-Side (SxS) stuff recently, and this is par for the course. One thing that not many people seem to know is that you should be able to get a lot more information on why this error is occurring by looking in the event viewer.

Either do Start|Run eventvwr, or go to Start|Control Panel|Administrative Tools|Event Viewer. Look in the System section for entries where the source is SideBySide. You should see a couple of errors generated for each failure.

The first one is generally not much use, but the second one contains the error that caused the failure. This is often:

  • A syntax error in the application configuration file (myapp.exe.config)
  • A dependent “assembly” specified in the manifest is missing

The config error is easier to fix, as these are external files that can be edited with any text editor. Manifest errors are more difficult as they can also be embedded in the binary as a resource.

Greedy Baby Unleashed

After several years gestation, “Greedy Baby”, a DVD collaboration between my friend Bob Jaroc and electronica impresarios Plaid has finally been unleashed.

As well as enjoying the DVD, I’ve also been to many of the live shows – including at the London IMax – and it’s impressive stuff. Forget the faux-psychedelic rave visuals you might have seen elsewhere, this is a truly mind-bending and intelligent aural-visual union.

Go get it…

Fahrenheit

I’ve just finished playing Fahrenheit, an intriguing game.

It caught my attention because it sets out to be different from many games of today. For a start, it’s not a sequel or subtitle (”Game: the second”), it’s new IP, and more importantly it recognises that games can be all about storytelling and direction. In fact, the game sets out to blur the line between participant/player and storyteller.

I was a bit disappointed by the visuals, partly I think from being spoilt by Half Life 2. The lip syncing and face rendering in general is nowhere near that level of fidelity. The character animation is also a little weak, there are obvious switches between motion capture sequences (walk to run etc). But when playing the game you really don’t notice these things. It is so immersive and engaging that your suspension of disbelief easily carries you past these aspects.

There are a combination of elements to the game. “Traditional” walking around, exploring a scene, looking for interesting aspects to investigate (which reminded me of Resident Evil in many ways; looking for that glistening item in the dark room) and the interactive actions, driving conversations and performing physical actions. This is a similar to the QTE mechanism in Shenmue (or at least the Dreamcast version that I played), although it’s more widely and better used. It consists of using the controls (the keyboard and mouse in my case) to ape the actions of the character on screen, to perform Track and field style button bashing for effort, or to follow Simon-says style colour sequences. They work well as a means to link you physically to the character in the game world, for example, using the controls to breath in a slow and measured way to keep the character calm. My main complaint about this is that I found myself focusing on the control overlays on screen, rather than the action that my character was performing behind them! Given some of the fantastic sequences the game contains, this is a shame.
The game is a little on the short side; it left me wanting more, but in many ways I prefer this to being forced to perform mundane/frustrating tasks that serve to artificially lengthen playing time. So often nowadays I never get to see the end of a game, despite investing hours of time. I did however, feel that the story had a slightly rushed feel towards the end, given the lesiurely pace at the start of the game. If this was a deliberate attempt to reach some sort of crescendo, it didn’t quite work.
Reading this back, it all sounds a bit negative: it isn’t meant to be! Despite it’s quirks this was a thoroughly enjoyable and interesting game to play. If you’re into different gaming experiences I’d definitely give it a try.