Formula based pricing. Storage in RDBMS and use in application layer

Question

I'm about to enter the 1st phases of designing a commodities trading application. A major function of this application is the capture and evaluation of pricing information. Pricing in this market is not fixed. It is usually a market benchmark +/- a premium. For example: (A and B are market benchmarks)

• Price = A + $1
• Price = A + .5(A-B)

The final price of the deal is calculated using the pricing formula applied over an agreed time. For example; The final price might be the average of A + $1 over the week prior and week subsequent to loading the commodity.

At present the traders are capturing this detail in Excel which allows them the flexibility to setup the formulas required, but this information never makes it into our accounting systems and the spreadsheets are not connected to live predictions of the market benchmarks making price forecasting a manual and slow task.

I'm looking for input as to what tools / techniques I can use to enter, store and evaluate formulas in an application.

We will be using SQL Server or Oracle as the database and the client platform is not fixed yet, but you can assume WinForms/WPF, ASP.NET or a Java webapp.

I know this is a pretty broad question, but we're not far enough along to be more specific as to what technologies will be used.

Thanks

Answer 1

I bet your users like the flexibility of entering the formulas into Excel, so I'd suggest an approach like this:-

1. Write something to allow them to enter formulas into your application using the type of operators that they actually will use with keywords for the different base prices. This can just be stored away as a string.
2. Validate this using a white-list approach (could be fairly tricky, maybe you'll need to look up some stack-based parsing algorithms). You might allow: + - / * ( ) {numeric constants} {keywords for base prices}.
3. Translate these formulas into something that your database can understand in the SELECT list. If the formulas that they enter are similar to what you can do in SQL this might not be too difficult. The keywords get turned into column names and everything else can be left alone.

Then, you'll be able to SELECT back the data, averaging over the time periods, etc by plugging in the special formual and running it over your historic data and/or predictions.

Step 2 has some tricks to it, but it would make everything else easy (and efficient).