Saturday, September 19, 2009

Building a financial mini-app

Building a mini-app from scratch using only text files and the command line is a bit like building a piece of furniture from IKEA. I find that after five steps I realise I made a mistake in step 2, and I have to pull everything apart again and start almost from scratch.

When I was creating the financial transaction table, I decided to dodge the date conversion issue and make the date field text only. After all it was my intension to read the data back into Excel or Access for a GUI presentation at the end of the day. But when I had imported the data and started to query the tables, I decided I needed to sort by date during processing. So I had to drop the table, with all its data, and create a new one with a real date field.

The irony is that, such are the quirks of MS Access, it was much easier exporting to a CSV file with the exact date format, that Apache Derby was looking for, than getting it back. When you export a CSV file Access gives you many choices on how to export dates, but when you suck it back in it seems a lot more fussy. But that is of no importance. I brought the data back into Access for display purposes only, so a string was just fine at that point.

For the processing itself I used two resultsets and a loop within a loop to scroll through them. This required two open statements. I also needed a third open statement to modify certain records and delete others in the raw data table.

The first resultset brought up all sales transactions. The outer loop scrolled through that. The second resultset was called by the outer loop, and brought up all purchase transactions for the stock code of the current record in the first resultset. The inner loop scrolled through the second resultset accumulating the numbers of shares purchased until it was equal to or greater than the number of shares sold. In the case of inequality an adjustment was made.

To illustrate, here is an extract from the raw table in MS Access.

ID Date TrType Code Price Qty
81 18/03/2008 Buy ANZ $20.78 50
206 7/11/2008 Buy ANZ $16.42 60
221 13/11/2008 Buy ANZ $15.00 65
228 17/11/2008 Buy ANZ $13.50 70
348 13/07/2009 Buy ANZ $14.40 695
352 5/08/2009 Sell ANZ $19.70 -50
362 9/09/2009 Sell ANZ $22.19 -290

And here is the corresponding data in the new table.

ID Date TrType Code Price Qty
352 2009-08-05 Sell ANZ $19.70 -50
352 2008-03-18 Buy ANZ $20.78 50
362 2009-09-09 Sell ANZ $22.19 -290
362 2008-11-07 Buy ANZ $16.42 60
362 2008-11-13 Buy ANZ $15.00 65
362 2008-11-17 Buy ANZ $13.50 70
362 2009-07-13 Buy ANZ $14.40 95

At first glance, the impact of the work was not especially great. But on closer examination there are two key changes. First, the individual transaction code in the first table has been replaced by a sales code in the second table. Second, the data has been reordered such that each sale has associated with it just enough from the buy transactions to cover it exactly.

Looking at the actual data, the first sale was easy, because there was a buy which exactly matched it. But the second sell straddles three complete buy transactions and part of a fourth.

If anyone can do that in SQL, I'd like to see it. The only method I could think of was using good old fashioned code, as described. It was a bit of effort, but it was worth it, because I now have a report which rigorously and systematically matches sales with purchases, and tells me at a glance what my exposure to realised profits is in the current year.

No comments: