wilmott.com

I am attempting to implement the gas storage model of Boogert and de Jong. I have written the code in VBA and I am getting reasonable results. However, the model is taking an unacceptably long time to run (think overnight) when the original paper sights run times of under one minute. Part of it may be VBA and I am sure that there is some sloppy programming that I can tighten up, but I've narrowed down the code responsible for the long run times and I think it has to do with all the regressions required in the method. I am running probably 2500 regressions or so. I am using - application.worksheetfunction.linest() - for the regressions.Does anyone know if this particular method is run time consuming and if there is a better method?

I think that you would need to move to do your code on C++, had the same problem when doing 15,000 simulations.

how many data points in your regression ? if it really is the regression that is slowing it down, why don't you hand code the regression equations ? that should be faster than calling linest.

Thank you both for your suggestions...C++ is probably a good long term solution, but I would have to jump through some corporate hoops to use anything but VBA (my company has chosen an "official" programming language after an analyst left and his code was all in some language that no one else knew).Each polynomial regression (in my case, degree 6) has 1 data point from each of the monte carlo simulations, right now, that is 1,000 data points.It is not clear to me that coding the regression is faster than calling linest, but I guess linest does a bunch of stuff that I don't really need or want. I think it is a logical next step.Thanks again for the help.

use alglib

Thanks.I am not familiar with alglib, but I will look into it.

I am hesitant to ask the author when I believe he has a similiar model for sale. I have had a couple of bad experiences with that (with other people). I would be more willing to contact him if I had reason to believe he wouldn't mind.I am not 100% sure that I have implemented the algorithm correctly, but I am sure I have implemented it exactly the way I understood the paper's implementation. I spent a fair amount of time sketching out that piece of the original paper. Of course, I could have misread the algorithm.I may be doing slightly more regressions than the original author; I did not implement one of his pricing algorithm reformulations, but it should be very close. However, I may be using this on a longer time frame than he was intending. I was originally planning on this being a monthly tool. I am now being asked to price 2 year deals with it. Although, it looks like the author anticipated using it for annual contracts. Guess I'm not really sure.

You might want to make the time step bigger than one day, which would save you time (weekly or monthly) instead of daily.

outrun - thanks. I am attaching the version of the paper I used. First column of page 87 is what I used to determine the methodology and number of regressions.http://www.energyquants.com/assets/uplo ... od.pdfpunk - Thank you. A reasonable idea, but I added some functionality to the back end of the model and I need daily results to derive my new results.