Markets and Excel and VBA, oh my. February 20, 2008
Posted by James Webster in : finance, development , trackbackSpend any time at a fund manager or investment bank and you will quickly realize how much of life is lived inside a spreadsheet. And when I say spreadsheets I mean Excel.
A great deal of those Excel spreadsheets also have a great deal of VBA. I have heard many of my professional developer colleagues pooh pooh VBA but the fact remains that there is a hell of a lot of VBA code written in Excel to implement complex financial models and derivatives pricing (albeit in the latter case, interfacing via COM or XLL add-ins with analytics libraries written in C++). I have spent a significant amount of time working and interfacing with VB/VBA code for this purpose in my day job.
The big question is, what will happen to VBA? It has already failed to make the cut for the latest version of Microsoft Office for Mac. This article over at The Register did claim that VBA would be removed from Office when the next version after Office 2007 was released, around 2009, however Microsoft has since said that is not the case and a retraction has been posted. That said, I expect that Microsoft would very much like to deprecate the VBA runtime environment in Office due to the additional support burden on top of .Net integration with Office.
However the existing base of users who consider themselves Office developers are unlikely to simply adopt Visual Studio Tools for Office with its additional ‘burdens’ of compilation, object orientation and static typing… although having said that I do want to avoid igniting a religious war! Porting from VBA to .Net/VSTO is also a non-trivial process.
So a new product like Resolver One from Resolver Systems is quite interesting to me. It is basically a spreadsheet application that is highly programmable in Python (via, I believe, IronPython) supporting integration with .Net and therefore everything that .Net can integrate with. Resolver System’s Giles Thomas describes it better than I in the YouTube video below.
Having had a quick look at the Resolver Systems forums however I believe they may have an upward battle in getting their product broadly adopted (which they may not need to be a commercial success at any rate). Excel is absolutely entrenched and for Resolver One to completely replace Excel in many environments they will need parity all the way down to the look & feel and user interaction level. One suggestion provided by another user was for Resolver One to operate as an Excel add-in; quite how that would work I am not sure, maybe they would employ something like Managed XLL to dynamically create Excel functions that bind to the Python methods in the Resolver view of a workbook.
However if Resolver One could interpret the VBA code in an existing Excel spreadsheet, either by dynamically or statically converting it into Python or IL or whatever it runs under the hood, they could have a very interesting product on their hands. It would be very appealing to the IT shops of investment banks that have a pool of Excel spreadsheets for which they wish to scale calculation out across their grid (assuming they haven’t gone down the Excel Calculation Services on Computer Cluster Server route for whatever reason). Effectively implementing their own VBA runtime on top of .Net may also ease porting of VBA to .Net languages by allowing the VBA code to be incrementally ported.
A few other thoughts about Resolver One:
- I wonder if they have considered decoupling the calculation engine from the front-end to the extent that a Resolver One spreadsheet could be passed to a service running on a DataSynapse grid and processed across multiple nodes for Monte-Carlo simulations and the like.
- Perhaps they should look at Visual Studio Shell as a possible replacement for elements of their own GUI, although Microsoft’s licensing terms may prohibit that for products that are a little too close to competing with their own stable.
And a few other interesting links about Excel:
- Joel Spolsky discusses why you shouldn’t think about writing your own code to parse Excel and other Microsoft Office file formats.
- The Register’s Dominic Connor states a case for blaming the credit crunch on rampant VBA but also why Microsoft would be mad to discontinue VBA support any time soon… it would spark a user rebellion.
Comments»
Hey James,
Thanks for taking a look at Resolver One! A couple of thoughts:
To confirm what you may have suspected - we’re not looking to replace Excel in general, just to provide an “upgrade path” for the people who are building spreadsheets that are large enough that the issues with grid/code interaction start arising, but don’t want to start full-on programming. However…
Of course, for this to work well in many cases we’d also have to support Excel’s addins. This scares me…
We’re certainly interested in working out a way to get Resolver One to act as something like a replacement for the recalc engine inside Excel - that is, using Excel as a front-end but replacing the back-end. When we’ve looked at this in the past, it didn’t look doable - perhaps things will have changed with VSTO, though.
Converting VBA to Python is certainly something we’ve considered; my own gut feeling is that we’re unlikely to be able to get a conversion that produces decent, maintainable code, but you never know. I suspect this will be client-driven - if people tell us “we *will* buy Resolver One if it does that” then we’ll do it, but otherwise we won’t
For grid computing - this is certainly something we’re keen on looking at in the medium to long term. Interestingly, one of the guys at Digipede (who I believe are a DataSynpase competitor) tried using their product with Resolver One in quite the opposite direction - instead of building a model in Resolver One and then distributing it across a grid, he used Resolver One as a front end and data manipulation tool to access a pre-existing .NET/distributed infrastructure. Here’s his blog post about it: http://westcoastgrid.blogspot.com/2008/01/hey-excel-resolverone-understands-net.html
Re: Visual Studio Shell - I’m sure we could go in this direction, and it would save us a lot of time. We do worry, though, that it would pretty much block off later creation of a Linux version: VS is a windows-native application, so it would stop anyone from running Resolver One under Mono. That said, the grid component we’re using at the moment isn’t Mono-compatible, so it’s a moot point. We’ll have to see what happens there.
Anyway, thanks again for looking at the product - I hope the above is of interest!
Regards,
Giles
It is definitely quite interesting. The Visual Studio Shell point is a very good one, as a Mac user myself it would be good to see Resolver One on other platforms some day!
I’m looking forward to having some bandwidth to play around with this and come up with some demos for folks at work!
[…] news from Zoho, given that I have previously thought about a runtime for VBA implemented in .Net… Zoho Sheet now has support for VB macros (via TechCrunch). Since this runtime is implemented […]