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:

Date	Vendor	Amount	Notes
16/07	A	£10	Shoes
17/07	B	£5	Bags
17/07	B	£5	Bags
18/07	C	£8	Hats

<tbody>
</tbody>

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

Date	Vendor	Amount
16/07	A	£10
16/07	D	£2
17/07	B	£5
17/07	B	£5
18/07	C	£8

<tbody>
</tbody>

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

Date	Vendor	Amount	Notes
16/07	A	£10	Shoes
16/07	D	£2
17/07	B	£5	Bags
17/07	B	£5	Bags
18/07	C	£8	Hats

<tbody>
</tbody>

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.