How to keep track of bank accounts (spreadsheets)?

Brie

I use a spreadsheet to list my credit cards only.  This lists the amount currently owing and how much needs to be paid off on what day including what the DDs are - sometimes these are different dates so noted separately where required.

Never used one for accounts although I probably should now that I am responsible for 5 here and another 8 overseas.  Generally I have passwords noted on the spreadsheet for the cards and in a book for the accounts but never written out in full.  So I might have "A2" listed which to me might mean "Alpha248".  

Eco_Miser

My system is way too complicated - it grew over more than two decades. I have log in details plus card numbers, pins, phone numbers in my password manager.

I have a sheet listing each account- columns for institution, account type, sort code, account number, opening date, maturity date if relevant, current interest rate, free-form notes. Closed accounts are moved to a section at the bottom until there is no possibility of there being a query about them.

I have a sheet listing the interest rate changes for variable rate accounts - two columns per account - date and rate, with the account name and number as headers across both columns.

I have a sheet listing transactions - columns for date, amount, from, to, method, reason. From and to contain short codes for the accounts, and for people/organisations who pay me or I pay, including INT for interest and CSH for when I draw cash.

Another sheet in the same workbook uses the SUMIF() function to total the transactions for each account and arrive at the balances.

Then there's the workbook in which I attempt to predict future transactions, and particularly where I need to make a manual transfer.

And the workbook that summarises the annual totals back to 2005, and the workbook listing all the Direct Debits, and the ones trying to predict the future, and ...

As I said, far too complicated.

Jami74

Mine has evolved over the last five years too, it started as a spreadsheet to log spending on our one account and now involves multiple accounts and multiple sheets. 

I like being able to compare spending to previous years and I do forcast forward too.

Reckless_Saving

I use MS Money, the final version Microsoft made free and its available in various places online.  I just hold basic details, account name, transaction date, amount, category, I may include a comment if I feel it’ll be useful to know in the future like a big ticket item that maybe replace long in the future and I want a remind of what I knew at the time.

I spend a few minutes a week updating it and all my transactions going back over 20 years stored in it.

inspectorperez

foshank said:

inspectorperez said:

If you are a Windows user, there is a very useful app called Moneypoint which helps you maintain all transactions on your bank accounts. This is available FOC in Windows Store

I find it very useful as a forecasting and budgeting tool as well as recording actual transaction histories. There is a memorandum section for each account where you can record S/C and account numbers etc. It's very versatile and you can export reports into Excel spreadsheets if you want to tinker with the way information is presented.

I use this in conjunction with a spreadsheet which records month end balances on all bank/investment accounts and assists in acting as a forecasting tool for investment decisions on fixed term bonds/ISAs etc.

I have just downloaded Moneypoint but I have been unable to find any written manual or useful youtube video to give a basic intro to the app. Have you or any other user found any help? Thanks

If you look along the tabs on the Home screen, you will see one entitled "Demo". If you click on this, you will see a "Get Started" button which allows you to create test data before you start using the software for real. There are also basic instructions on how to set up new accounts and enter transactions.

From my own experience, I started by adding one bank account and inputting real data. I recall I entered one month's transactions and then started examining the reports etc to see how they worked. I found it quite intuitive tbh and have been using it for over a year now as replacement software for Visual Accounts which I had been using for countless years beforehand.

phillw

km1500 said:

Excel.password will not be bypassable (if xlsx) but obviously can be subject to brute force dictionary attack hence don't use words in the spreadsheet password

AFAIK Passwords are only effective in Excel 2013 and later & a lot of people still use 2007.

foshank

inspectorperez said:

foshank said:

inspectorperez said:

If you are a Windows user, there is a very useful app called Moneypoint which helps you maintain all transactions on your bank accounts. This is available FOC in Windows Store

I find it very useful as a forecasting and budgeting tool as well as recording actual transaction histories. There is a memorandum section for each account where you can record S/C and account numbers etc. It's very versatile and you can export reports into Excel spreadsheets if you want to tinker with the way information is presented.

I use this in conjunction with a spreadsheet which records month end balances on all bank/investment accounts and assists in acting as a forecasting tool for investment decisions on fixed term bonds/ISAs etc.

I have just downloaded Moneypoint but I have been unable to find any written manual or useful youtube video to give a basic intro to the app. Have you or any other user found any help? Thanks

If you look along the tabs on the Home screen, you will see one entitled "Demo". If you click on this, you will see a "Get Started" button which allows you to create test data before you start using the software for real. There are also basic instructions on how to set up new accounts and enter transactions.

From my own experience, I started by adding one bank account and inputting real data. I recall I entered one month's transactions and then started examining the reports etc to see how they worked. I found it quite intuitive tbh and have been using it for over a year now as replacement software for Visual Accounts which I had been using for countless years beforehand.

Many thanks inspectorperez. I will have a go later today. Your suggestion of inputting real data for a single month and examining reports is sensible advice. I already have an Excel spreadsheet with several sheets for different a/cs etc. Hopefully I can learn to implement / transfer data as quickly and seamlessly as you appear to have.

Jami74

inspectorperez said:

If you are a Windows user, there is a very useful app called Moneypoint which helps you maintain all transactions on your bank accounts. This is available FOC in Windows Store

Thanks for this. I've been playing with it today and like it more than any other free money/budgeting app/templates I've tried. It seems to do pretty much what my spreadsheet does. If I had started with this I probably would never have developed such a complex spreadsheet for myself.

Fellwalker

I use MoneyDance on Windows 10.  I actively use excel with macros yet would not want to trust it with my bank transactions.  It is too easy to press delete or overtype something, and I've seen that happen too often. 
I started many years ago with Quicken, but after 2004 they stopped providing it to the UK.  I tried MS Money but it felt such a downgrade. I spent a year trying different available options, then found Moneydance. It copes with multiple accounts, and I've used it for administering my dad's estate. All entries can be categorised, and noted if subject to tax.  You can run multiple reports including one to do your year end tax if necessary.  It imports ofx files, csv files from your bank. It has add-ons for downloading from paypal, and for stocks and shares prices. I can even synchronise it across my laptop and desktop. The only downside is the app for phone, which is next to useless.

Billxx

Daliah said:

I record each and every transaction for my current, savings and investment accounts , so I always know down to the last penny how much money I have where. I also record SOs and DDs which are auto-filled into the respective current account at the relevant dates. Expected maturity dates and amounts for Notice accounts and Regular Savers are also added, at the time I set up the accounts.

As there is a Category assigned to each transaction - e.g. taxable interest, tax-free interest, switch bonus, transfer, dividend,  withdrawal etc etc - I can easily run summary reports for a given category and tax year, calendar year or any other time period. I find this particularly useful for the interest and dividend income I need for my Self Assessments.

I don't record account number per se, but my account naming convention lets me easily identify individual accounts. Login information for all accounts is stored in a password manager.

Me too!  And I balance each account when statements by the Bank/Provider are generated.  Also all account number, sort codes, reference numbers.  No passwords.