Portfolio Computation on 3 Yrs. worth of Historical Data (approx. 200MB)

[ec2046]

I am not sure what the best approach would be to set up system through which my buddy can run simulated calculation historical data.

I thought about setting up a large Excel spreadsheet or setting up a database to hold the data.

I don't know really what the pros and cons of each approach would be.

Basically, the problem is he wants to take sell options with hypthothetical strike prices on assets whose historical data he has. He wants to do this each month for a few years and check the outcome.

[shabinesh]

sounds little data warehousing!!! but 200MB is not such a big data and doesn't worth for expensive apps. Imho, You can put it in a database and write your own logic to process it. :)

[Glenton]

Basically you're trying to figure out how high-level you want to go.

Python (perhaps with numpy) would be a relatively easy way of doing this kind of thing. Can easily churn through line by line doing calculations etc. Relatively quick to pick up. It's a high level coding language (and open source, so no investment other than time to pick it up).

Excel could handle it (probably), but it would be bang-your-head-against-a-wall painful. E.g an hour to run through, discover your mistake, rerun etc. Vs python probably milliseconds to run. Basically excel is too high-level.

C++ would be crazy overkill, and is nowhere near as easy to pick up as python (which is much higher level). It's basically too low-level for something as (relatively) trivial as this.

Access *could* be a good option depending exactly on what calculation you're after. I personally found python easier to pick up than access. I'm not an expert on relational databases, but if you have a bunch of lilnes of data and want to do calculations on each line, then it's great. If you want to go through the lines, building up the calculation as you go (e.g. most simulations), then it's not as good.

[Jerry Winston]

I'll have to agree with Glenton here. Doing this kind of historical trend analysis and predictive modeling would be excruciatingly painful in Excel. Let me explain. Creating the functions would be very easy in Excel, however, the program would run slower and slower with each refresh and each report.

I would recommend using any free RDBMS available online. You have express/free versions of Oracle, MySql, MS SQL, and DB2 readily available from their respective corporate sites. I chose the RDBMS because they have data engines that are particularly tuned to quickly processing data. Excel has to consider color, font, formatting, spell checking, and a host of other operations. All of these actions eat up valuable CPU and memory causing your Excel solution to slow with every additional line of data. The modern RDBMS puts alot more CPU to work performing the calculations your data needs.

200 MB wouldn't be any strain for the RDBMS systems I mentioned.