Modeling Structured Finance in VBA

Landed our first securitization deal. Was asked if I am interested in doing the securitization modeling or should we outsource to the big four auditing firms or someone in India for doing it on the cheap or saving us time. I volunteered that I will do it (it will be good for my CV and allow me to play around with my hobby of coding).

Brought out William Preinitz “A Fast Track to Structured Finance Modeling” and read almost 300 pages into it in a few hourse. After having used python for some time, verbosity of VBA was like a culture shock. Secondly, I tried to follow the coding examples of Preinitz in my own data file. Late into night coding by copying and I realize that I am not learning much. So I start a new file and start coding in VBA from start without referring to the book example yet following the same methodology. I of using a 2D array to load the whole raw data into memory, I follow Preinitz’s methodology loading each column of data in its own single array. This being VBA and “Option Explicit” feature switched on to prevent unintentionally creating variables (by typo), it required firstly declaring a global array variable, secondly ReDim that array to length of column and finally loading the column data in to that array. I have to do this for each column I have to load. Similarly, to do collateral screening, I have to refer to each data column by name.

I had presumed that may be its faster to load data into 1D array and test it by going array by array rather than loading all of it up in a single 2D array because that is why Preinitz goes about doing it. I googled around a bit and whoa.. was I surprised. With the current computer speeds, its faster to load the whole dataset as a single 2D array. Moreover, I do not have to create complicated variable names to refer to data. I can go to them by just using column numbers. Fucking hell. This makes my life so much easier. Fewer variables to create

I will plan to test this today or tomorrow night but God, Preinitz couldn’t have made it harder. Who knows, when the program increases in complexity, I might regret this but common sense tells me I won’t.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s