If you have ever downloaded bank activity from your bank’s website, you know there is some amount of cleaning you need to do before the data is ready to use. Perhaps the next step is to summarize the bank activity with a PivotTable, or, perhaps to perform a bank rec. Regardless of your ultimate objective, you’ll need to spend time importing it into Excel and then cleaning it up. This post demonstrates how to use a Get & Transform query to import downloaded bank activity into Excel and prepare it for use.
Objective
Before we get too far, let’s take a look at our objective. We have logged into our bank account and exported the activity for a date range. The bank’s website exports it to a CSV file, a sample of which is shown below.
Before we can use it in our next step, whatever that may be, we’ll need to tidy it up a bit. For example, we need to remove columns C & D, we need to distinguish between deposits and charges, and we need to classify the type of charge based on text in the description. Then, just because we accountants enjoy this type of thing, we’ll summarize it in a PivotTable to confirm it matches our bank statement. I’ve created a short video as well as a written narrative below to demonstrate how to accomplish all of this.
Video
Narrative
We’ll walk through this using the following steps:
- Import CSV
- Clean
- Load to PivotTable
Let’s just jump right in.
Note: The steps below are presented with Excel for Windows 2016. If you are using a different version of Excel, please note that the features presented may not be available or you may need to download and install the Power Query Add-in.
Import CSV
First, you download the bank activity from the bank website as a CSV file. Then, in a new blank Excel workbook, you click the Data > From Text/CSV command in the Get & Transform ribbon group. In the resulting Import Data dialog, browse to the CSV file. Excel provides a preview of the data, as shown below.
Click the Edit button to open the Query Editor, where we’ll Clean the data.
Clean
The CSV data is loaded into the Query Editor, as shown below.
Inside the Query Editor, we can apply a series of steps to clean the data.
1) Let’s remove unnecessary columns, in our case, the 3rd and 4th columns. To do this, we click the column to delete and use the Home > Remove Columns command.
Note: we can also group-select multiple columns by holding the Shift or Ctrl key while selecting the columns.
2) Let’s define our column labels. To do this, we double-click the existing Column label, enter the new label, and hit Enter. In our case, our new column labels are Date, Amount, and Description.
3)Now, we can create any new columns we may need. In our case, we want a new column that describes the type of transaction. This will be useful when we return the data to Excel and summarize it with a PivotTable.
There are many options in the Add Column ribbon tab worth exploring, but the one we are after now is the Add Column > Conditional Column command. This opens the Add Conditional Column dialog, where we can define the rules.
The first rule will label positive amounts as deposits. So, in the dialog, we create the first rule: If the Amount column is greater thanthe value0 then we want the Output to be Deposit. Then we click the Add rule button and move to our next rule.
The second rule will identify the transactions that have a description that contains the text BILL PAY as a Check. So, we create the next rule: If the Description column containsthe value BILL PAYthen the Output is Check. We click the Add rule button to create our next rule.
The third rule will identify the transactions that have a description that contains the text PURCHASE AUTHORIZED as a debit card purchase. So, the next rule is: If the Description column contains the value PURCHASE AUTHORIZED then the Output is Debit Card.
If none of these conditions are true, then, we set the Otherwise value to Charge.
Note: these rules are case sensitive, so, if the descriptioncontains BILL PAY then the dialog needs to be BILL PAY and not Bill Pay or bill pay.
The final rules look like this:
Click OK and bam … the new column appears in the Query editor as shown below.
Now, we are ready to return the data to Excel.
Load to PivotTable
To return the query results to Excel, we can click the Home >Close & Load To command.
To see the results in an Excel table, we select the Table option. Now, we can summarize the data with a PivotTable by clicking any cell in the results table and clicking Insert > PivotTable.
We then structure the PivotTable report as desired by placing fields into the desired layout area. For example, we can insert the Amount field into the Values area, and the new custom column into the Rows area, and the resulting PivotTable is shown below.
And the best part is that next month, we can simply download a new CSV file from the bank website, delete the old CSV, and save the new one in the same folder with the same file name. Then, use the Data > Refresh All command and bam … it is done! The PT now includes all data from the new CSV file.
If you have any other fun Get & Transform tricks, please share by posting a comment below!
Sample CSV file:SampleFile
