No, this post isn’t a rant on consumerism and lack of true holiday spirit. I have another blog for that. This is my programming blog. I was thinking hard about what I could post to close out the year. It doesn’t seem much has been happening.
But I recently moved from MS Money to Quicken and some of the changes between the two are a little jarring. The biggest difference is in the register, where the running balance only calculates the displayed transactions. So if you filter the view by date or status, the running balance has no useful meaning. I find this flaw infuriating, as do other former Money users, but Quicken users have dealt with it from the introduction of register filters.
So, me being the “oh, it can’t be that hard” kind of person, I decided to test the waters and see how difficult it would be to re-write MS Money in .NET. Aside from a lot of the financial rules and calculations used in the investments section, I’m not discouraged yet. Last night I went through the screens in Money and whipped up some basic object diagrams.
And then I started working on some conceptual programming ideas to make sure I wouldn’t hit a wall. The first thing I wanted to make sure I could handle was a running balance in the register. It must be pretty difficult if Quicken can’t do it.
I thought about all the different ways a register would be modified and the effect of those changes. But there was one difference between Quicken and Money that pointed me in the right direction. When you create a new account in Quicken, your opening balance is the first transaction for the account. In Money, your opening balance is stored in the account and there are no transactions. This said to me: Quicken cannot calculate a running balance without having that first transaction available. However, Money can. What is Money doing differently?
I don’t know the exact answer to that question, but the solution I came up with was to create a field in the transaction (tx) table that stored the difference, or delta, from the opening balance. That way, I could take any single transaction or group of transactions, and get the running balance by adding the delta to the opening balance. I wouldn’t want to store the actual balance in there because if the opening balance was ever changed, I’d have to update EVERY transaction.
So in simplicity, we have two tables that look like:
CREATE TABLE Account(
ID int IDENTITY(1,1) NOT NULL,
Name nvarchar(50) NOT NULL,
OpeningBalance money NOT NULL
) ON PRIMARY
CREATE TABLE AccountTX(
AccountID int NOT NULL,
TXAmount money NOT NULL,
AccountDelta money NULL,
TXID uniqueidentifier NOT NULL,
TXDate datetime NOT NULL
) ON PRIMARY
And of course the tables have appropriate defaults. A simple query with populated tables would be
select a.name,tx.txid,tx.txdate,tx.txamount,
a.openingbalance+tx.accountdelta RunningBalance
from accounttx tx
join account a on a.id=tx.accountid
order by tx.txdate
But we want the database to manage the AccountDelta field itself. We can’t be expected to make all the updates
whenever a user changes the TX amount or moves the date forward or back. So we’ll handle this with triggers.
First, the insert. We’ll assume that a new transaction is always entered at the current date and time (which
isn’t realistic, but just play along) and that only one row is inserted at a time. The trigger would be:
CREATE TRIGGER dbo.Add_AccountTX
ON dbo.AccountTX
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
declare @LastDelta as money
-- Get the previous delta from the last transaction
select top 1 @LastDelta=AccountDelta
from AccountTX
where TXDate < getdate()
order by TXDate desc
-- add the new row with the adjusted delta
insert AccountTX(AccountID,TXAmount,AccountDelta)
select AccountID,TXAmount,isnull(@LastDelta,0)+TXAmount AccountDelta
from inserted
END
We’re using an INSTEAD OF trigger so we can completely control the insert. And it’s pretty simple - we get the
last delta and add or TX to it when we insert the row. The DELETE trigger is pretty easy as well - we just
deduct the TX amount from any future transactions.
However, when updating, the user could be changing the TX amount, which affects all future deltas, or they could
change the TX date, which will change the deltas from the earlier of the old TX date or new TX date.
ALTER TRIGGER [dbo].[Update_AccountTX]
ON [dbo].[AccountTX]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON
if update(TXDate)
begin
declare @txAmount money,@txDate datetime,@TXID uniqueidentifier,@LastDelta money
declare priorUpdates cursor for
select old.TXAmount,old.TXDate
from inserted new
join deleted old on old.txid=new.txid
where old.txdate=@txDate
fetch next from priorUpdates into @txAmount,@txDate
end
close priorUpdates
deallocate priorUpdates
declare updates cursor for
select new.TXAmount,new.TXDate,new.TXID
from inserted new
order by new.TXDate asc
open updates
fetch next from updates into @txAmount,@txDate,@TXID
while @@fetch_status=0
begin
-- get the previous delta
select top 1 @LastDelta=AccountDelta
from AccountTX
where TXDate<@txDate
order by TXDate desc
-- Update the delta for the row moved
update AccountTX set AccountDelta=isnull(@LastDelta,0)+@txAmount
where TXID=@TXID
-- Update all future transactions
update AccountTX set AccountDelta=AccountDelta+@txAmount
where TXDate>@txDate
fetch next from updates into @txAmount,@txDate,@TXID
end
close updates
deallocate updates
end
else if update(TXAmount)
begin
declare @diff money,@minDate datetime
declare updates cursor for
select new.TXAmount-old.TXAmount Diff,new.TXDate
from inserted new
join deleted old on old.txID=new.tXID
order by new.TXDate asc
open updates
fetch next from updates into @diff,@minDate
while @@fetch_status=0
begin
-- Update all future transaction with the difference
update AccountTX set AccountDelta=AccountDelta+@diff
where TXDate>=@minDate
fetch next from updates into @diff,@minDate
end
close updates
deallocate updates
end
END
Yeah, it’s bulky. There’s two sections depending on whether the amount changed or the date changed. The
date is difficult because you have to handle TX’s moving forward and backward, and it’s essentially doing an insert
and a delete. The change to the amount is simple, it’s only twist is having to determine how much the
difference is to apply to future deltas.
But, the point of the exercise was to see if I could do it at all, and I feel comfortable with what I’ve got.
The bulk of the code will be in display of the transactions and the investment logic. If Money’s UI isn’t
already written in WPF, then that would be an excellent fit for the design. We’ll go slowly on this. I
need to make sure that I have some other concepts worked out as well.
But that will be discussed in the coming year. Good riddance, 2009.