Running Bank Statement

[spencerellis90]

Hi guys

For work I have to keep an Excel version of our company card bank statement and day by day I have to update it, and then annotate it on Excel with notes. When exporting the Excel version of the bank statement, sometimes the order changes and a transaction or two appear amongst transactions I already have in my spreadsheet, which means I am manually having to identify what is new and what I already have, so I know what to copy and paste in and what not to.

Is there a way I can easily update my running statement whenever I need to, without duplicating transactions?

Things to note:
- Sometimes I may have two identical transactions with regard to date, vendor and amount on the statement; simply removing duplicates won't work
- I annotate my Excel sheet so I can't just copy and paste over what I have in my Excel running statement - I need the transactions I already have to remain in the spreadsheet with my annotations.

Any help would be hugely appreciated.

Thanks,
Jake

Also posted here.

 

[Scott T]

Things to note:
- Sometimes I may have two identical transactions with regard to date, vendor and amount on the statement; simply removing duplicates won't work

How do you know what is a duplicate to be deleted and what is a identical transaction that should not be deleted?

 

[spencerellis90]

How do you know what is a duplicate to be deleted and what is a identical transaction that should not be deleted?

Hi Scott

I only know because I'm currently manually adding in new transactions and then laboriously checking it matches the statement. Soon I won't be able to manually add in new transactions, and for the reason you mentioned I can't just remove duplicates.

I need my statement to be updated to match the statement I download, without losing my notes.

To put my issue in to context, in basic terms:

On Monday I download a statement for the first time. I add a column for my notes and this becomes my master statement:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Vendor[/TD]
[TD]Amount[/TD]
[TD]Notes[/TD]
[/TR]
[TR]
[TD]16/07[/TD]
[TD]A[/TD]
[TD]£10[/TD]
[TD]Shoes[/TD]
[/TR]
[TR]
[TD]17/07[/TD]
[TD]B[/TD]
[TD]£5[/TD]
[TD]Bags[/TD]
[/TR]
[TR]
[TD]17/07[/TD]
[TD]B[/TD]
[TD]£5[/TD]
[TD]Bags[/TD]
[/TR]
[TR]
[TD]18/07[/TD]
[TD]C[/TD]
[TD]£8[/TD]
[TD]Hats[/TD]
[/TR]
</tbody>[/TABLE]

On Tuesday, I download the same statement, but some transactions have been added and changed the order:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Vendor[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]16/07[/TD]
[TD]A[/TD]
[TD]£10[/TD]
[/TR]
[TR]
[TD]16/07[/TD]
[TD]D[/TD]
[TD]£2[/TD]
[/TR]
[TR]
[TD]17/07[/TD]
[TD]B[/TD]
[TD]£5[/TD]
[/TR]
[TR]
[TD]17/07[/TD]
[TD]B[/TD]
[TD]£5[/TD]
[/TR]
[TR]
[TD]18/07[/TD]
[TD]C[/TD]
[TD]£8[/TD]
[/TR]
</tbody>[/TABLE]

How do import the only the new transactions (highlighted in orange) to create the below:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Vendor[/TD]
[TD]Amount[/TD]
[TD]Notes[/TD]
[/TR]
[TR]
[TD]16/07[/TD]
[TD]A[/TD]
[TD]£10[/TD]
[TD]Shoes[/TD]
[/TR]
[TR]
[TD]16/07[/TD]
[TD]D[/TD]
[TD]£2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17/07[/TD]
[TD]B[/TD]
[TD]£5[/TD]
[TD]Bags[/TD]
[/TR]
[TR]
[TD]17/07[/TD]
[TD]B[/TD]
[TD]£5[/TD]
[TD]Bags[/TD]
[/TR]
[TR]
[TD]18/07[/TD]
[TD]C[/TD]
[TD]£8[/TD]
[TD]Hats[/TD]
[/TR]
</tbody>[/TABLE]


Thanks,
Jake

 

[Scott T]

Without something in the file to tell what should be deleted and what should not I do not see of doing this except to manually use the statement as you currently are.