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

Question

Hi,

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

Interesting scenario. WARNING: This response is based on some ideas I had for approaching the problem and may not reflect best practices.

I would try to see if you can boil down your potential formula rules into simpler constructs. This would then allow you to compose your rules together to create complex formulas. You would then need to be able to store the rules within the database which could be done using either a DSL or some XML rule file.

It sounds like your current scenario, formula's in excel, is working out fairly well but has a few hiccups. Have you considered using some advanced macros or excel automation? You could probably achieve most of your goals without your clients ever having to leave excel.

Good luck.

Answer 2

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).

Answer 3

If the data is from a trusted source (big if) you can store database expressions in a column and then create a SELECT statement on the fly to execute it.

Answer 4

You could use a symbolic math package such as GiNaC in order to interpret the formulas you enter into the tables.

Answer 5

Have you considered integrating with Excel? That way you don't have to try to pry Excel out of their clutches (business users ADORE Excel), and they get to use all the tricks they've learned over the years. You just pump the data back and forth between your app and Excel using VBA (or however they tell people to integrate with Excel these days) and you're pretty much good to go.

Answer 6

I have a ridiculously simple solution related to Hank''s proposition. Create a database and model it after your Excel tables. Export the Excel data and import it into the your database. Create ODBC connectors on the client machines to point to the database. In Excel, you can connect to the data sources. This way, your clients will have no issues with UI changes.

Answer 7

Thanks for the responses.

The users themselves want to get away from Excel. They are battling with multiple users trying to enter and update data. I might investigate what options VSTO gives for embedding small Excel sheets into applications.

I'm leaning towards using an embedded 'language' to store and evaluate the formulas.

Using a simple parser to turn entered formulas into SQL is probably a reasonable idea as the variables in the equations are time series vectors not scalar data points and the SQL approach should make it easier to compute the results over the required time periods.

Thanks