"Don't use VBA" is of course one natural response. But the trader worksheets (with live reuters data feeds) are going to be in excel. Take that as given. So I start to translate several-line cell formulas into VBA functions. The formulas are mostly lookup and averaging (various commodities swaps, eg). I still lean on VLOOKUP and other native excel functions, in the vba. But I can already see a 1 to 2 second lag, even before tackling the big SUMPRODUCTs for longer strips. I've never made an add-in or dll. Would that be a way to keep speed, more comparable to cell formulas? (Note, this is not for electronic market making, so there's not a need or expectation to try to approach light speed. But with the little bit I've done, I can already see my new draft getting more than a penny or two off. Which makes it hard to justify the "improvement" of readable VBA code.)

A) If staying with VBA learn to use variants effectively to speed things up. See I recommend going with an add-in, C++ preferably for speed but if you want readability and ease of use ExcelDna + C# is a good option.

QuoteOriginally posted by: mosta"Don't use VBA" is of course one natural response. But the trader worksheets (with live reuters data feeds) are going to be in excel. Take that as given. So I start to translate several-line cell formulas into VBA functions. The formulas are mostly lookup and averaging (various commodities swaps, eg). I still lean on VLOOKUP and other native excel functions, in the vba. But I can already see a 1 to 2 second lag, even before tackling the big SUMPRODUCTs for longer strips. I've never made an add-in or dll. Would that be a way to keep speed, more comparable to cell formulas? (Note, this is not for electronic market making, so there's not a need or expectation to try to approach light speed. But with the little bit I've done, I can already see my new draft getting more than a penny or two off. Which makes it hard to justify the "improvement" of readable VBA code.)Give the nxll library a whirl. If you can write C++ code, this makes it easy to create addins. Be sure to write platform independent C++ code and use the library as a thin wrapper to call that. Eventually you will outgrow Excel and need to call your code from a productions system. You can then just link to your C++ library and get the same results as you see in the spreadsheets.

Moving your formulae into VBA is a great way to simplify your spreadsheets, and with some care the performance should not be a big problem. But the built-in functions like SUMPRODUCT are really fast, and even writing your function in a C/C++ .dll is quite a bit slower than using the built-in functions. It probably makes sense for you to grow and optimise your VBA library first, and then to try other options for higher performance once you're comfortable and ready to experiment a bit.For a start, there are lots of tricks for making VBA run faster - a good start is Charles Williams's FastExcel blog (http://fastexcel.wordpress.com/2011/05/ ... fs-part-1/) and tools (http://www.decisionmodels.com/index.htm).Moving beyond VBA, a great way to get significantly improved performance with fairly good code compatibility from VBA is to use VB.NET and the .NET framework. You'd integrate your VB.NET user-defined functions with Excel using my Excel-DNA library. Compared to C#, you'd have the same performance and access to the full .NET framework and libraries, but a familiar syntax and copy-and-paste from VBA to help you get started. Of course there is quite a learning curve getting used to the .NET framework and Excel-DNA, but the speed-up from VBA can be between 2x and 10x, and you get to easily use advanced .NET libraries like the parallel libraries for stuff you really need to optimise. Mike Staunton had a nice talk last year on making portable functions that are easy to move from VBA to VB.NET, and through a translating tool even to C++. A video where he discussed how he does this is here: http://www.thalesians.com/finance/index ... /Seminar27.

Thanks everyone for the suggestions. I have no C/C++. I did a little project at home in C# to learn the basic syntax etc. I think I have enough C# to do the things I need (but there would be a lot of gritting of teeth if I had to replace excel vlookup and match functions with looping through arrays to find...). I've done a little VB.net at home too, and that, as mentioned, is closer to what I have done in VBA (where I have made a point of using class design, as opposed to being a macro-recorder devote'), should I move out of VBA. I think I may have landed on the heart of my issue just by clicking on the fastexcel blog link:"One reason its slow is that there is a large overhead each time a VBA program transfers data from an Excel cell to a VBA variableAnd this function does that lots of times (3 times 32000)."There are tables where I considered programming the entire range at once, but instead used simpler single-cell formulas and code. So I'll try moving to an array formula.One point I'm still not clear on is whether there is any advantage to taking the VBA out of a module and into an add-in/dll? Would that avert or mitigate the issue of having the code interact with the worksheet that the quotation mentions? Or would the situation be exactly the same (which is what I would guess)? And if there is an improvement to VBA add-ins/dlls--for a really elementary question, do I need Visual Studio to do that (not the Express version)? Also, for the path of Excel-DNA or nxll, do these avoid that same bottleneck of interacting with Excel? Finally, just a little more clarification, I'm not running Monte Carlo sims or numerical approximations to diff eq here, or anything massively computational. Just replacing formulas that have up to half a dozen or more SUMPRODUCT, MATCH, VLOOKUP, and such calls (and I'm not replacing them to try to be faster, just clearer and easier to support/extend). It's replacing just some of the simpler ones (no big sumproducts even) that I already see the significant lag. And am I guessing correctly that the real bottleneck or lag is just passing between Excel and VBA? Ie, that the task I need to achieve is not to try to outperform Excel SUMPRODUCT with something in C/++/#Thanks again for all the comments. I've already learned valuable things.

Both Excel-DNA and Keith's nxll libraries are called from Excel through the native C API. This is the interface MIcrosoft recommendeds for 'high-performance' user-defined functions, and the call overload is pretty low.Also, by using the .xll interface your functions can run multi-threaded under Excel 2007/2010, in the case of Excel-DNA add-in by just adding an attribute to your function.For checking the per-function call overhead, I can give some indications from checks I did a while ago on my five year old Centrino laptop, with Excel 2007. For trivial functions (take double, compute, return a double) a C implementation using the Excel SDK could calculate about 1 million calls a second, while the C# library with Excel-DNA was doing between 300 000 and 150 000 calls per second, depending on settings. Some details here: http://exceldna.codeplex.com/wikipage?t ... ormance.So the per-call overhead of calling a managed function via Excel-DNA is a few microseconds or so.The bottleneck referred to in the blog post you quote refers to reading the data in a range via individual calls, each going through the Excel COM automation interface. The COM automation interface is not so fast for doing the many single calls, and the function is much faster (from VBA) when a single calls retrieves a whole array at a time.For transferring large blocks of data into and out of Excel sheets with a macro, the C API also gives much higher performance options. In an Excel-DNA add-in you can read a block of a millions cells, calculate a bit on each value, and write a millions cells back in under a second. Lots more discussion on this using optimised VBA and other tools here: http://stackoverflow.com/questions/3840 ... tExcel-DNA - Free and easy .NET for Excel

as you spotted the bottleneck is the 3 times 32000 ..can you clarify what exactly you are doing?you didn't clarify how you are taking the data ... in loops?effectively you should take the data in one? variant as hansi suggested as long as you vectorise properly ( ie avoid loops you should get pretty good performance with vba ( same principle as matlab, mathematica etc). doesn't sound like moving to c++ etc will be faster ...

Thanks much for the information particularly about the C and COM interfaces. I'm going to start by using array formulas and passing entire tables at once with VBA in user modules, which should (I hope) get me to match the speed of the current design with ugly cell formulas. So I can get a really quick turn around, like a couple few days. Once everything is in order for a v. 2, I'll look into a v. 3 using dlls/ xlls, etc, to get better deployability than sharing workbook files. To clarify for spv or anyone, the data flow is as follows: the input is reuters data feeds of dynamic market info, eg for, let's say, 8 nearest term commodity futures, and then beyond that some liquid 1-month, 6-month and 12-month futures spreads. The output will be, first, interpolated single-month futures prices. So this is a table of about 100 rows. Then there is another table of 100 rows of swaps on averages of pairs of the futures (uneven weighting). Then swaps are calculated in longer strips of months, maybe with more weighting. So the results are lots of averages with weighting, some discounting and some fx translation. (There are options too, but in separate workbooks and they won't rely on continuous live data updating.)

QuoteOriginally posted by: mostaThanks much for the information particularly about the C and COM interfaces. I'm going to start by using array formulas and passing entire tables at once with VBA in user modules, which should (I hope) get me to match the speed of the current design with ugly cell formulas. So I can get a really quick turn around, like a couple few days. Once everything is in order for a v. 2, I'll look into a v. 3 using dlls/ xlls, etc, to get better deployability than sharing workbook files. To clarify for spv or anyone, the data flow is as follows: the input is reuters data feeds of dynamic market info, eg for, let's say, 8 nearest term commodity futures, and then beyond that some liquid 1-month, 6-month and 12-month futures spreads. The output will be, first, interpolated single-month futures prices. So this is a table of about 100 rows. Then there is another table of 100 rows of swaps on averages of pairs of the futures (uneven weighting). Then swaps are calculated in longer strips of months, maybe with more weighting. So the results are lots of averages with weighting, some discounting and some fx translation. (There are options too, but in separate workbooks and they won't rely on continuous live data updating.)Sounds like something xllarray was built for. Do ARRAY.SET on the data from Reuters and call the built-in SUMPRODUCT function using ARRAY.GET. One advantage to this approach is that it can handle variable size arrays.

Restructuring your formula into array calculations may help speed things up (ctrl+shift+enter). Any functions that require searching through data (e.g. MATCH) are very slow. Minimize. It is very slow to get data into and out of VBA. Use Resize to output a lot of data. Set rngAns = rngStartCell.Resize(ubound(vData,1), ubound(vData,2)) rngAns = vData

you might also try controlling the flow of calculation through vba commands such as; Range.Calculate and Range.CalculateRowMajorOrder (very fast) and minimising use of 'volatile' functionsMore details can be found here: http://msdn.microsoft.com/en-au/library/aa730921.aspx

Lots of useful information in this thread. One idea I'm working on now is experimenting with Excel-DNA for the first time. I'll use VB instead of C# I think, just to stay closer to the VBA I already have. Now since I'm not compiling dlls in an IDE, it's a lot trickier to test code--no stopping for a peak at the locals window. What I'm trying to figure out is how much of native Excel I can use in this approach. One point is that I presume that any range object that I pass as an argument to a UDF will arrive in the code as an array? If so, then I won't be able to use methods like Resize on my range object parameter? (One way I was thinking of maybe getting around this was sending Range Addresses instead of Ranges as arguments to the xll. But I don't as yet see how to do that with worksheet functions.) Also, if I declare an Excel application object to utilize SumProduct and Match and VLookup, should I expect to not have much speed advantage over VBA?

QuoteOriginally posted by: mostaLots of useful information in this thread....Also, if I declare an Excel application object to utilize SumProduct and Match and VLookup, should I expect to not have much speed advantage over VBA?Have you tried xllarray? I think it can solve your problem, but you may have to supply more details. It uses the little known FP data type to make things blazingly fast in Excel as long as you are using only numbers.

For learning Excel-DNA, the best place to get support is the Google group at http://groups.google.com/group/exceldna.It you want to use an IDE to develop and build your VB.NET add-in, you can try the VB.NET edition of Visual Studio Express or the free SharpDevelop IDE. Getting the debugging hooked up has a little trick, but then it works well.From your Excel-DNA add-in you have access to both the full C API and the Excel COM automation model that you know from VBA. If you set the type of your argument to be double[,], you'll get passed the double array via the FP type Keith refers to (for numbers only). If you set the argument type to 'object' and mark it with an <AllowReference:=True> attribute, it will give you an 'ExcelReference' if so entered by the user. From the information in the ExcelReference you can even build an Excel Range object.From your VB.NET add-in, using the SumProduct, Match and VLookup function through the C API (the ExcelDna.Integration.XlCall helper type) is easy and should be fast. Though you could get hold of the Application object and call the function that way, it can be slower since you are making COM calls through the automation interface.

Hi Kimosabe, I have not tried xllarray yet--but I am interested in any new and interesting and useful things. I didn't start with xllarray for something new as yet, for a couple reasons. One is that I have zero experience with C/C++ (but I do have a little C#). Also, I wasn't sure if the raw array math addins would address my goal of simplifying cell formulas. To give you more background, a typical formula that I am trying to replace would take a start date and an end date as user inputs, for arguments, then it would look in a table with contract months, days per month, monthly prices, and monthly discount factors, to compute a weighted average. The current formula is: SumProduct(prices, days, discount factors) / SumProduct(days, discount factors). Each of the SumProduct arguments is an array, which is identified by using Match on the start date, and a hand-figured datediff in months, with these two as arguments to the Offset Function (using both the row offset and range resize functions that are within Offset). So in summary, we have 5 sumProducts, 5 Matches, 5 Offsets, 10 Month calls and 10 Year calls, for a formula 4 and 1/2 lines long. Would not xllarray only replace the SumProduct function? Leaving most of the lengthy formula work for identifying the vectors? What I've written in VBA takes prices, days, dfs, dates as a single range argument. I use Match once on the date, and VBA Resize 3 times, to pull column arrays out of the table/Range. I guess a point I'm getting at here is that in some places I need to make a table where I know how it is going to go, month by month from this month. Other places I need to compute an arbitrarily sized weighted average from user inputs.