MS Excel and Banking

Managing Bank Accounts & More with MS Excel

Well, you may ask “Why would I ever do this or even need this?” But have you ever said to yourself - “I had an unexpected bi-annual charge [such as car insurance], and it overloaded my checking account!” Or, “I am not sure I can afford that because I don’t know its impact down the road!” Or, “How can I easily see how my expenses have changed through the past months or years?”, or “It takes me so long to collect my tax data!”

This MS Excel finance tracking may not be needed or wanted for those who always have plenty of funds in their banking, but for those who do not, this may be a god-send! Here are the advantages of implementing such a financial tracking system:

  1. You are much more apt to keep abreast of your financials when regularly tracking them online.

  2. You can easily view how you have been spending recently, and during the past.

  3. You can easily track spending amounts per item or groups of items, simply based on the name you entered by doing searches.

  4. If you use Projection (highly recommended), and if you do, you will be much more likely and accurate to see how today’s expenditures impact tomorrow’s.

  5. You can just as easily track your bank accounts and all credit cards and other loans.

  6. You will find it very easy and expedient to summarize your expenses and income for the whole year for tax purposes.

Setup Checking Account First

The first step is to setup your Checking account in MS Excel.

  1. Open a new MS Excel sheet, and open your bank account online.

  2. Create the exact same headers in MS Excel as are shown in the bank statement.

  3. Copy the bank data and paste it into the MS Excel worksheet and name it (at the bottom) ‘Ledger’.

Since banks and credit cards typically have the most recent transaction at the top of the page, which has a great implication on how you setup your MS Excel worksheet. At this point you have a choice to make – either:

(a) Keep the Excel data in the same sequence as the bank, or

(b) Enter the data in reverse order.

Keep Data in Same Order as Bank

With choice (a), you will be inserting the banking data at the top of the MS Excel worksheet always, which means making enough room (enough new rows) in Excel at the top each time you bring over the bank data. Some banks transfer one MS Excel row for one line in the bank statement, some transfer 2 rows per each transaction, and with some banks they insert many more rows for special messages, so you have to learn this about your bank statement. A simple rule is to insert twice as many rows than what you expect. This will result in removing extra blank lines below the newly inserted bank data. And if this is not enough new blank row, than ‘Undo’ in MS Excel, and insert the number of more rows need, and re-paste the bank data.

Store the Bank Data in Reverse Order into MS Excel

This method is a bit easier to manage and you never have to insert rows because the new bank data is always inserted at the bottom of your Ledger worksheet. With this method you may not choose to simply copy over the bank data because it will always be in reverse order, with the newest data at the top of your insert data instead of at the bottom, and you want the newest data to be at the bottom/end of the Excel worksheet. You can either do the copy and paste, and then reorder the rows within MS Excel to match the bank data exactly. Or you can simply type in the bank data line for line in the correct order.

Projection

Projection is one of the major benefits of using MS Excel to track and manage your banking. The goal of projection is to be able to see how spending ‘today’ will impact ‘tomorrow’. In order to create this, you will need at least one full month of tracked expenditures, and it is best to get at least one full calendar month of bank data into your MS Excel worksheet. Once you have this one full moth, you can copy this and append it to your existing data.

If you choose Option (a) above, then you will have to count the number of rows used for the month you plan to copy, and insert that number of blank new rows at the top of the data. ‘Copy’ & ‘Paste’ this month of data, and then convert all the dates to the new month.

If you choose Option (b), then simply append the copied month at the end of the data. Then convert all the dates of this new month to the month it should be.

Balances

Maintaining a balance is key in this whole process of using MS Excel to track and manage your expenses. Most banking statements will include a balance at the end of each day, and none for pending items. But most credit card statements do not, and may give a balance for each transaction in the last monthly statement (or the current one), and none for any past expenditures. In either case, a balance column must be created and maintained for both types of banking systems. In the case of the checking account banking, there should already be a column for the balance. But you should not use or rely on the transferred over banking balance.

You need to create a formula in the balance column that updates automatically and correctly with each transaction. This formula can be as simple as take the current balance and subtract and expenditures or add in and credits. So, some banks will have separate columns for debits (expenditures) and credits (deposits). If yours does, then build your MS Excel worksheet with the same columns and the headings at the top should reflect this IF your choose Option (a) above. If you choose Option (b) then you are entering in the data manually, which means you can take it or leave the dual columns – one for credit and the other for debits. If you ‘leave’ it, then you can simply make the deposits as a positive number, and the credits as negative numbers, and just use one column for the actual transaction amounts.

This balance should always be the exact same value as the bank shows, and if it ever is not, you either typed in the wrong data or did not transfer the correct data. Figure out where the deficiency is right away without continuing on. This formula needs to be propagated up into the projected data as well, which makes the projection much more accurate. Propagating this balance just means copying the formula up and over all the transferred balances and the propagated balances, and converts all of the new bank data from specific number to a formula. Make sure that the Bank balances and the MS Excel balances match exactly!

Other Financial Accounts to be tracked

Once you have created the Ledger that tracks and manages your bank account (usually your checking account), you are ready to add into the Excel file more worksheets to track and financial account you have that you find useful to track. Each one of these just becomes another worksheet, so be sure that you named the first one as ‘Ledger’ (double click on the worksheet tag and simply type in the name, and then click elsewhere when done). The following is a list of financial type accounts you may wish to track:

  1. All credit cards

  2. Insurance accounts

  3. None real estate loans

  4. Utilities

To create any of these, it is very similar to creating the Ledger. Again, you can choose Option (a) or Option (b) with these as well. If you setup your credit card (or any other financial worksheets) to be copied from the credit card online statement, then you should be the columns for this data that same as the form it is in at the online credit card statement. Of course, this will force you to append new data at the top, and always to insert new blanks rows. If you choose Option B for the credit cards (and all other extra worksheets), then you should setup the format to be the same as that of the Ledger worksheet, such that all the columns are exactly the same so you can easily ‘Copy’ & “Paste’ the whole Ledger data row into the appropriate worksheet without piecemealing the data.

Linking

If you choose to add on to the Ledger worksheet with any of the above suggested financials that are to be tracked, then linking becomes very useful and effective. The basic idea here is to take any transaction data from the Ledger, such as the payment amounts, and their dates paid, and copy this data to these other financial accounts you wish to track. Copy just the amount, or just the date, or the whole entry from the Ledger, depending on how you setup these extra worksheets. Then either ‘Paste’ or ‘Paste Link’ these bits of data into the appropriate worksheet, or copy the whole row from the Ledger onto the appropriate extra worksheets.

The linking guarantees the validity of the other financial data since it came from the actual payment from the Ledger. The ‘Paste’ will copy only the data into the other worksheets, whereas the ‘Paste Link’ inserts an actual link (instead of the raw data) and if the source changes (the source here being the Ledger), then this value will change as well.

Advancing the Projection Data

After doing all of this, you should end up with at least one worksheet (Ledger), and hopefully many more for other various financials you wish to track. After running this for a few months, try to keep 3-4 months in the projection are, the data with dates past the last bank date. Again, to create this projection area, simply duplicate a month you think is for typical for you, and paste it several times until you have 3-4 months ahead, changing the date for the each new prospect month to the value for that month. To change the month, simple go to the ‘Home’ tab, off to the very right top, click on ‘Find’ or ‘Select’ or ‘Replace’, and highlight just the date column. Then use ‘Replace’, insert the number of the month that you copied and pasted in the ‘from’ box, followed by a slash (‘/’) representing the old month number. Then in the update box, put the new month number, followed by again a ‘/’, and say change all. Make sure you only highlight one month at a time.

Summations and Taxes

At the end of the year you can fairly easily sum the data for the whole year, which is really good for tax purposes. I suggest building a summary box after each year, which is just a number of entries that are to be summed, surrounded by a border (creates a box) so it stands out easily. There are two ways to get the totals in this summary box (and you can easily use both):

  1. Manually run through the Ledger data, adding up the entries you see that should be included for each sum.

  2. Let MS Excel do the summations for you, and use equations (formulas) that will run through the entire data for that year, looking for specific text or certain specific amounts, and Excel will add the appropriate ‘amount’ cells, giving you totals.

This will give you entries for the sums desired, and you can group some of these together for totals, such as how much paid in property taxed (perhaps from several properties), received in income (perhaps from several sources or other people), business expenditures, utility expenditures, groceries, interest paid, and so on.

Conclusion

In conclusion, if you take the time to create this Excel file, the rewards are very great. You will easily be able to see if you can afford that expensive item by what it does to your future balance. You will be more in tune with your spending activity, being forced to seeing it due to your updating your MS Excel file often. And perhaps who in the family is spending it!