The Banking & Savings Spreadsheet Discussion Thread

sausage_time

https://forums.moneysavingexpert.com/discussion/comment/81862032#Comment_81862032

Pivot Table may be better than multiple SUMIFs. Steep initial learning curve, but life changing.

AndyTh_2

I collect all my accounts transactions into Excel workbooks (in Tables), categorised, and use VBA to combine them into a single dataset and pivot table all that to produce income/expenditure/balances/self assessment reports. It's awfully horribly lovely. Would be too much for Google sheets.

even got it calculating interest on dates due in the future (formulas not VBA), but not including much budget cashflow

Tried BeanCount with Fava, so as to also track investments, but not gotten round to a switch. As clunky as the VBA is, it's stable and low maintenance. Tried PowerQuery but not able to produce the aggregation so completely with account metadata connected.

IAmRobbo

If you are going to use Excel spreadsheets, I can't stress how much using tables will make your life easier.

masonic

https://forums.moneysavingexpert.com/discussion/comment/81862204#Comment_81862204

A pivot table is very helpful for cataloguing interest payments for self-assessment. You can just have a simple table of [date / account / interest received] for data entry, and then pivot this into month vs account to view a 2D representation with monthly totals, account totals, and the overall figure. Allowing easy tracking of where you are as you go through the tax year, and where your interest is coming from if needed.

Reducing the input data to a simple flat table is so helpful in many situations as it gives you a lot of flexibility in reshaping the data in an automated way.

AndyTh_2

https://forums.moneysavingexpert.com/discussion/comment/81862373#Comment_81862373

yep, makes reading formula's much easier when they are referencing the table header field for getting current row cell or column data when it's not a tiny table too, as well as auto managing the range size and filters

qsk

Does anyone has a Excel template to calculate total savings of your regular savers at a particular day (eg, today)?

Bridlington1

https://forums.moneysavingexpert.com/discussion/comment/81863312#Comment_81863312

For regular savers I usually do the totals on a monthly basis but include a balance prior to deposit column, then add a temporary new column at the end for getting the totals for the end of the month. In most instances this would give the same result given I fund most regular savers at the start of the month.

E.g. From January's table I'd do the below for accounts I've fully funded:

Bank/building society	Name	Interest rate	Balance prior to deposit	Min deposit	Max deposit	New balance
Saffron BS	Members Month Loyalty Saver	8.00%	£350.00	£0.00	£50.00	=SUM([@[Balance prior to deposit]],[@[Max deposit]])
Principality BS	6 Month Regular Saver Issue 4	7.50%	£1,000.00	£0.00	£200.00	=SUM([@[Balance prior to deposit]],[@[Max deposit]])
Progressive BS	Online Regular Rainy Day Saver Account Issue 4	7.00%	£900.00	£20.00	£300.00	=SUM([@[Balance prior to deposit]],[@[Max deposit]])
Harpenden BS	18-30 Regular Saver	6.00%	£0	£0.00	£200.00	=SUM([@[Balance prior to deposit]],[@[Max deposit]])

You can then just copy the contents of the new balance column into the balance prior to deposit column as values only (ctrl+shift+v) to get the below, which forms the basis for February's table:

Bank/building society	Name	Interest rate	Balance prior to deposit	Min deposit	Max deposit
Saffron BS	Members Month Loyalty Saver	8.00%	£400.00	£0.00	£50.00
Principality BS	6 Month Regular Saver Issue 4	7.50%	£1,200.00	£0.00	£200.00
Progressive BS	Online Regular Rainy Day Saver Account Issue 4	7.00%	£1,200.00	£20.00	£300.00
Harpenden BS	18-30 Regular Saver	6.00%	£200.00	£0.00	£200.00

For accounts I've only funded with the minimum I would get the new balance by doing =SUM([@[Balance prior to deposit]],[@[Min deposit]]).

If you like to colour code things on your spreadsheet, you can highlight those you've fully funded and sort the spreadsheet by colour to make it quicker to add these formulas in.

Note this is a small section of my own table for regular savers, I've removed most of the accounts and a few of the columns otherwise it'd take up a lot of room. I include a next interest date column so that I can see which ones I need to adjust the balances manually if interest is paid before the accounts mature.

david72

Possibly easier to set up and manage than a spreadsheet is a dedicated accounts program to keep track of your finances (but if twiddling with a spreadsheet is what works for you, then that's fine too).

I very much like GnuCash, which is free open source software, is available for Linux, MacOS and Windows, has been on the go for over 25 years, and is very professional and full-featured (I send them a small donation every so often in recognition of the utility I get from it).

All you need to do is add each account to the ledger when you open that account, and then add the transactions as you make them (you can set up regular transactions (such as deposits into regular savings accounts) to be added into the ledger automatically, to save you the manual effort), with the ledger keeping track of the updated balances in both the sending and receiving account. You can use it to keep track of your household expenses as well.

LHW99

https://forums.moneysavingexpert.com/discussion/comment/81863799#Comment_81863799

Unfortunately, these dedicated accounts programs use double entry book keeping.

Calculus, differential equations, even a bit of quantum mechanics OK, double entry I cannot make sense of 😣

masonic

https://forums.moneysavingexpert.com/discussion/comment/81863836#Comment_81863836

It's not so hard to fathom, is it? When you move money between two accounts, it is debited from one account and credited to the other. If, for example, you send money from your current account to a regular saver, you see an entry in the statement of both accounts. Double entry extends this to create "accounts" for income and expenditure too, allowing you to track where your money is coming from and going to, so that all transactions can be represented by transfers between accounts. So you can have a "taxable savings interest" account that gives you a full record of interest you may need to report/reconcile with HMRC, for example. And an "energy" account that captures transactions to/from your energy provider(s). You need only enter the transaction on one side of the transacting accounts.