Sunday, September 20, 2009

Acronis True Image Hanging

One of my clients uses Acronis True Image Home for their nightly back-up. I don't know the product well, but I like what I've seen so far - mostly. It seems pretty thorough. The backups run like clockwork every night. And I have successfully recovered a system which caught a nasty virus, such that a reformat was the only cost effective fix.

But the other day, the front end application, the management console, would not start. It just froze, with a flash screen reporting that it was checking disk D. Quite why it needs to check Disk D before opening is a mystery, especially because it is not included in the backup and it need never need be read from. On the machine in question D is the manufacturer's "recovery" sector. Quite frankly they are a waste of space, and the word recovery is a complete misnomer - "Factory Reset" sector would be more honest and appropriate. And given that nothing useful is ever written there, it mystifies me that Acronis should hang while trying to read that disk sector.

Be that as it may, it was hanging and I was in a quandary as to what to do. I tried going away and coming back a few hours later. It was stress reducing, but it didn't fix the problem. I tried searching on strings like "Acronis True Image Hanging", but all that told me was that Acronis seems to hang a lot, in a wide range of circumstances, and there doesn't seem to be any consensus on a fix.

So if there is something I would criticise about Acronis it is the heavy reliance on wizards. Perhaps there is a setting somewhere, which gives an "expert" view, and enables manual editing of tasks, but I certainly haven't found it.

My dilemma was increased by the fact that the nightly backup was working perfectly. All I wanted to do was change the backup folder, to initiate a new full backup and a new month long string of incremental backups. I didn't want to fiddle around uninstalling and reinstalling Acronis, because then I'd lose my working scheduled task. I was quite tempted to run away, and not tell the client that anything was wrong, and let the incremental backups just go on for years and years in the same folder. But I decided that would be irresponsible.

After much deliberation I decided that there had to be a script somewhere, controlling the scheduled tasks, so I set about looking for it. I had a look in the "program files" folder, but there was nothing very promising there. All the dates were way too old. So I changed the folder settings to show both hidden folders and protected operating system files, and went to hunt for application data in documents and settings (all of this is in XP pro by the way). I first looked in the user folder, but there was nothing for Acronis there.

Then I remembered the option in the standard Windows install which says "Do you want this program to be available to all users?" So I checked out All Users\Application Data and sure enough, there was a directory called "scripts". Bingo! The file had a very funny name, and I won't print it here in case some malicious bot is probing my blog, but sure enough it opened with notepad, and it was just an ordinary text file with a script in full English. I hope no one from Acronis reads this and encrypts the next edition, because it made me like the product more. The path to the backup file was easy to find, and I just had to modify two characters to change it to the new folder for the current month.

To my enormous surprise, the script ran perfectly that night, and left the new full backup in the new folder as I intended. Next month I'll just go straight into the script and not bother with the GUI.

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.

Thursday, September 17, 2009

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.