Using Java with Shares

I have this weird belief that spending too long looking at a share portfolio is morally wrong, because it's only money. But then I get this guilt thing about a lack of due diligence. My compromise is to spend the weekdays on my main project, which is developing interactive assessment software, and a part of the weekend looking at and thinking about shares.

My traditional tools for this are Microsoft Access and Excel. And while the market was diving, and I was only buying shares, that worked fine. All I had to do was to track what I had bought, what I had paid for it, and what it is worth now. But now the market is rising again, and a handful of shares have risen by silly proportions, I feel I need to sell small amounts of them, at least to recuperate the original investment cost.

But I am doing quite nicely with baby bonuses and other means tested benefits, and I don't want to blow my income out of the water. So I need a quick but accurate means of tracking the cumulative effect of a series of small transactions. The typical scenario is a holding built up from say five buy transactions. A proportion of that holding is then sold. I am not sure whether the Tax Department imposes FIFO or LIFO accounting on such transactions. I should look it up, but for now I assume you can do what you like as long as you are consistent. I shall use FIFO.

So I need to parse through the holding, comparing each purchase transaction with each sale transaction. If the first purchase is greater than or equal to the first sale transaction, the calculation is quite simple. I can just apportion the total purchase costs over the number of shares being sold and subtract that from the sale proceeds to calculate the profit or loss on the transaction. But if the first purchase is less than the first sale, I need to apportion the sale proceeds over the number of shares in the first purchase. I then need to apportion the purchase costs for the second buy batch over the remaining shares in the sale transaction.

I am sure there is an abundance of software out there that does all this but there are two reasons for doing it myself. First my business model assumes a low cost base, which means not wasting money on expensive accounting or trading packages. Second it represent good practice at manipulating data in Java.

My first step will be to export my transaction table from Access to a csv file. My second step will be to create a new Derby database using the embedded driver. This database will not be accessed from an applet. I want it on my local machine, and I want it in a folder which is included in my regular working data backup.

The third step will be to create tables to store both the raw data and completed transaction data. The fourth step will be to write code to suck the csv data into the raw data table. The fifth and most difficult step will be to write code to extract data from the raw data table and build this into a nice neat completed transaction table.