PDA

View Full Version : Westpac(Trust) web site transaction download analysis program



Dolby Digital
27-04-2003, 12:42 PM
Greetings,

Has anyone seen, built, used any software that will analyse the bank account transactions you can download from the Westpac website and organise them and get totals for various categories. I don't want anything fancy like using M$ Money; before I build one myself.

Russell D
28-04-2003, 10:45 AM
You import the data into Excel as a .csv file and analyse it from there.
If you don't have Excel - well - you could print the file and do it manually.

blank_harry
01-05-2003, 12:59 AM
@ Dolby Digital

I wrote a excel macro to convert .CSV files from the national bank into an appropriate style to suit a cheque book ledger I had created in excel.

In that ledger there are, among others, three columns that hold credit and debit entries (supplied by the bank) and there's a running balance displayed as well (not supplied by the bank).

To calculate a running balance the first row in my ledger holds a given starting balance which is equal to a balance on a given date according to the bank.

Then, all subsequent entries (in the rows beneath the opening balance) hold data which I periodically update by taking a .CSV file from the bank and converting it into a format that my ledger supports.

There's probably many ways to come up with a formula to sum credit and debit entries that then display a balance.

This is what I did.

I created a formula and then copied it into several thousand rows beneath the starting balance row.

Here's the formula =IF(OR(E5>0, F5>0),SUM(G4,E5,-F5),"")

Basically, this formula says if there's a credit or debit in either or both E5 and F5, then take the closing balance (from the previous row's balance column G4) add the credit, deduct the debit, display the new balance.

If there are no credits and no debits, don't do anything, since the assumption is that there isn't any data inserted into that row.

That way you get around having a formula display either a $ - , or, a $0.00 in all of those rows which have yet to have data placed into them.

Of course you could use conditional formatting to hide the empty balances in the balance column if you wanted, but i feel this formula supplies an elegant response to that issue.

Once you have data formatted in a way that suits your tastes, as already noted, you could paste that data into another work sheet and, having created some column headers, use excel's subtotal function to do various things with it.

Hope this provides some additional insights for you!

chisp
01-05-2003, 01:27 PM
I once wrote a program that would incorporate WestpacTrust (and BNZ and Westpac Australia) transaction into an Access database.
I also wrote a program which automated the process of retrieving the transactions but it would break every time any of the banks changed the layout of their website :-[. I'm currently partway through doing a new program that uses the CWebBrowser2 MS component which should be a bit hardier.
Do you want me to let you know when it's done? For me it's not a high priority.

Christopher Pankhurst

chisp
01-05-2003, 01:48 PM
of course if the banks would do something useful like decide on an XML based standard and (let's not get my hopes up) an XML based webservice designed for computers in beautiful concert with HTML based for humans.... then it would be so much easier.

Perhaps the banks could add some sophistication to their security model and come up with the idea of "read only" id/pin combinations to work alongside what we already have - then I would actually trust Westpac Australia and their "Other Accounts" feature.

Enough ranting for now...
Christopher Pankhurst

Dolby Digital
01-05-2003, 06:06 PM
When M$ Money uses XML as an input option, the NZ backs might use it. I may end up writing an interface to Accounting program yet.

And yes I would be interested in your application (I might use perl to get the transactions off the site).