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

Comments

Post a Comment

Popular posts from this blog

A few notes on JavaScript

I am one the 99% of the world who has long nursed the misconception that JavaScript has something to do with Java. And why not? The name sort of implies it. It was only after I searched without success on the Java Tutorial site for some mention of JavaScript that I stumbled across the knowledge that JavaScript was in fact developed by Netscape, under the name LiveScript. Armed with that knowledge, it is not surprising to find the best reference information on Mozilla site. According to Wikipedia , the nonsensical name choice had something to do with Netscape wanting to cash in on the popularity and cachet of Java, and Sun wanting support for Java technology in the then popular Netscape browser. For a while only the Netscape family of browsers supported JavaScript. Microsoft later produced what it calls JScript to run in its browser. JScript is similar but not identical to Javascript. According to Foldoc JavaScript runs a hundred times slower than C , and ten
Read more

Forum Comments on Java Applets

It is so long since I last posted on the once was Sun/Java now is Oracle developer forum my account seems to have been ditched. While I wait to have it resurrected I've done a little searching in the Java Database Connectivity (JDBC) forum using the search term "Applet". My first search, using the default 3 month period, yielded nothing. No one had posted anything about or even mentioned Applets in the last three months. So either they are very easy to use and work very well, so hardly anyone has any problems with them, or maybe ... not very many people use them at all. My stomach was beginning to churn a bit, when I extended the search period to the current year - and with good reason. Somebody, a little bit like me I think, had written a game in an applet, and wanted to record top scores in a database. He described his connection problem in this thread , and the first reply began: Doing JDBC calls from an Applet is such a bad idea that I am glad y
Read more

Creating a Custom Swing Component

Image
I always return to my blog when I'm stuck, and I'm stuck right now. I want to create a custom Swing component. Specifically, I want something like a JProgressBar , with the following changes: In place of a flat Foreground color, I want a rainbow spectrum, showing only the red range for low Values, and the whole spectrum for values close to the Maximum value; Instead of a rectangular box, I want the progress "bar" to take the shape of a normal, or Gaussian, distribution curve; I want progress to be displayed by the area under the curve, rather than by a simple linear scale along the x axis. It's not that there isn't stuff out there. I have five tabs open in my browser, specifically addressing the creation of custom components in Swing, as well as the source code for the JProgressBar . It is that, like everything to do with Java, it is bloody difficult to read. I shall begin from the horse's mouth as it were, with an article on the Java
Read more