VERY simple spreadsheet to track a single bank account.

Korkyb

Hi all

My elderly mother in law wants to keep track of her bank account on her computer.

Is there a spreadsheet that will be simple to use and just has money in and money out (with a description of what the income expenditure is out with a running total.

It doesn't need to have any budgeting features but just a running total of her single bank account for each month.

She uses a windows PC and does have Excel.



Emphasis again on the SIMPLE :-) (All the spreadsheets I've found do too much and will just confuse her).

Thanks in advance.

eskbanker

She should be able to download data from her bank in CSV or XLS format, which can then be copy/pasted into a spreadsheet following the bank's download format?  Any such downloads I've seen always have columns for transaction date, description, money in, money out and balance....

Korkyb

Thanks

She has access to her bank online but wants to have a manual spreadsheet which is separate / stand alone from her bank account.

eskbanker

Korkyb said:

Thanks

She has access to her bank online but wants to have a manual spreadsheet which is separate / stand alone from her bank account.

Yes, that's what I was suggesting - download the transaction data from the bank and use it to update a sheet in the same layout saved locally (and/or in the cloud), independently from the bank's facilities.

Korkyb

I understand.

I'll have a fiddle with that.

Rob5342

Why does she want it? Is it too keep track of upcoming direct debts?

Korkyb

Yes direct debits / spending / bills.

I tried downloading her banks csv file (all they provide) - the resultant spreadsheet doesn't have much info on it and doesn't automatically calculate income or outgoings (sorry I should have said that would be useful to minimise human error).

Ideally it would look like:

date  /  description  / £ in  / £ out / / running total (calculated automatically)..

eskbanker

Korkyb said:

Yes direct debits / spending / bills.

I tried downloading her banks csv file (all they provide) - the resultant spreadsheet doesn't have much info on it and doesn't automatically calculate income or outgoings (sorry I should have said that would be useful to minimise human error).

Ideally it would look like:

date  /  description  / £ in  / £ out / / running total (calculated automatically)..

Not sure what you mean by automatically calculating income or outgoings - if the sheet is basically a simple list of transactions then it's up to the user to add functionality over and above that, but it shouldn't be difficult to add totals or subtotals on the sheet itself, or to add a pivot table to summarise semi-automatically by period.

Likewise 'running total' - presumably the download format includes balance but is she perhaps wanting something that resets to zero at the start of each month?  Is she wanting to use it on a regular basis during the month or just use it as a repository for occasional analysis?

The key point being that downloading the raw bank data is an enabler, but beyond that it'll need to be tweaked to reflect exact requirements - obviously the more functionality that's added, the less simple it is....

Yorkie1

It would be easy enough to set this up with 5 columns, as described above (columns A - E)
Row 1 is headings for each column
Row 2 would be the opening balance in cell E2
Row 3 onwards is entries

I think the formula for cell E3 (running total column) would be "=E2+C3-D3"
Then the formula for the next entry (row 4), i.e. cell E4 would be "=E3+C4-D4"

And you can drag the formula down the column E, to autopopulate with future entries in rows.

Someone else will definitely let you know if my excel skills are even rustier than I think!

boingy

Korkyb said:

Yes direct debits / spending / bills.

I tried downloading her banks csv file (all they provide) - the resultant spreadsheet doesn't have much info on it and doesn't automatically calculate income or outgoings (sorry I should have said that would be useful to minimise human error).

Ideally it would look like:

date  /  description  / £ in  / £ out / / running total (calculated automatically)..

You've just designed the spreadsheet you need. Those are your column headings.
The only calculation you need is the running total and that should be equal to the running total from the line above plus the amount in the income cell of this line minus the amount in the outgoing cell of this line.

Korkyb

I’ve never delved into excel before but that sounds doable.

I’ll have a crack when I get home.

thanks !