In this article, we are going to teach you how to create a budget spreadsheet by using information from your checking and credit card accounts.
The concept is quite simple. You start by getting all of your financial transactions organized in a spreadsheet and then you summarize your personal finances in the form of a budget worksheet. In many cases, it’s actually not that hard to set up and you can do it in any spreadsheet program you desire.
Here, we’ll walk through the basic steps on how to create the budget worksheet.
How to Create a Budget Using Your Checking and Credit Transactions
Step 1. Start by downloading all of your transactions into a spreadsheet. This can usually be done quite easily as nearly all banks and credit card companies have online statements that are easily downloadable. To get the transactions, go to your bank or credit card website, log in, and search for the page or tab that refers to something along the lines of “account history”, “statements” or “transactions”. Then, look for a link that says “download”. Most websites provide several different formats in which you can download the data. When using a spreadsheet, is is usually easiest to select the “.csv” format. CSV stands for comma seperated values, which is a universal format used to transfer data between two programs.
Select the month of data you want to download and click the button. If you have a spreadsheet program like OpenOffice (which I use) or Microsoft Office, the csv file will likely open automatically, or prompt you to set the delimiters on the file. Usually, clicking okay will open the spreadsheet with the data from the csv file. If you are using an online spreadsheet program such as Google Docs, then you’ll need to save the file and import it directly to your spreadsheet.
Do the same thing for each of your credit card and bank accounts.
Step 2. Organize the downloaded data and add category column. When you have all of the data downloaded for your transactions, you’ll need to make sure that the columns line up properly. For example, you checking download may contain the columns: date, amount, description, check number. And your credit card download may contain something like: date, amount, address, description. You’ll need to make sure that all of your accounts are loaded into the table and that all of the columns match.
You’ll also have to add a category column to your transaction list. This will be used to classify each item into your budget spreadsheet. The four columns that you really need in your spreadsheet are date, amount, category and description. Once you have all of your transactions organized into one long list and classified into a budget category, it’s time to create your budget spreadsheet.
The most critical part of organizing your data is to make sure that expenses don’t show up twice in your spreadsheet. For example, your checking account will likely show a payment to your credit card company. But if you download your credit card transactions then you’ll want to remove the credit card payments from your checking account. Make sure that you dont have any duplicate expenses and that you’ve accounted for all of your spending categories.
Step 3. Create your budget spreadsheet using the organized data. This part can be a little tricky for those not used to using advanced spreadsheet functions. To transform your list of transactions into an organized budget, you’ll need to create a pivot table, or in OpenOffice its called data pilot.
To create a pivot table, you’ll need to select the data range of your transactions and then select the “create pivot table” function. The function varies slightly based on your spreadsheet program, but with some trial and error you should be able to transform your list of budget items into a budget spreadsheet.
If you need help, we’ve created this video to show you how to do it visually.
Step 4. Reformat the budget you created. A pivot table is hard to format, and you may want to switch around how the categories are displayed. For example, you probably want your income at the top and your major expenses to follow. If you get tricky and add some extra sort codes to your data you could accomplish this with the pivot, but that can get a little too tricky. For that reason, we recommend copy and pasting the values from your pivot budget and then putting them in the format you have.
In the example we did in the video, we only used a few rows of data. However, in real life, you could have hundreds or even thousands of rows. The more rows you have, the more efficient using pivot tables in this method becomes.