Data on my day off: A printer-friendly Pocketbook report

Update: I have now refined some of the features of this report - you can download the new version of the template here.

It's no secret that I love Pocketbook. Hell, I even wrote a blog post way back in February about it. I love how it syncs with my bank accounts so that I don't have to manually enter every single thing I spend money on. I love how it automatically categorises transactions for me. And I love that it gives you the ability to analyse your transactions using the web app.

One thing that had bothered me though was the lack of a 'printer-friendly' option for the Analysis reports. I've even emailed Pocketbook to ask them to put such a feature in - they did get back to me and said they would look into it, but as of yet this functionality hasn't been added. So I've made one myself in Excel. It uses the fact that you can export your transaction history from Pocketbook, and then uses a Macro-enabled Excel workbook to populate a printable report. I'll step you through how it works and how to use it, and if you want to download it - I've made a template version that you can customise yourself. With more than 50,000 people using Pocketbook now, there must be others in the same boat as me. You can click on any of the images below to enlarge them.

1.   Get your transaction history from Pocketbook by logging in to getpocketbook.com, going to Transactions, selecting 'All time' from the Date Range drop down and clicking Export (CSV).

2.   Open the Pocketbook Report Template Excel Workbook. Click 'Enable Content', this enables the macros that are embedded in this workbook to be able to run. These macros are purely to refresh the PivotTables later on, and are completely safe - promise!

Copy and paste all your transactions data into the Excel worksheet named 'Source Table'.

Note: Unfortunately, the 'Source Table' does not automatically update. You will need to do this manually each time you want to run a new set of reports with the latest data included. This is not an issue for me, as I do these once per month, so it's not a big task to do manually.

3.   Go to the 'Source Data' worksheet tab. Here I have listed all of the default categories as set by Pocketbook. If you have any custom categories that you've created, you will need to add these to this list manually. It's probably easiest to add rows in the middle of this, so that the formulas fill automatically afterwards. Just make sure you spell your category names correctly!

This tab is probably the most important, as it puts the data in a format that can be used more easily for the report. It splits up the 'Amount' column from the Source Table (which lists expenses as negative and income as positive) into two columns and turns expenses into a positive number by taking its absolute value. It simultaneously collects all of the transactions listed under each category, within the particular date range that you'll specify later.

Note: For this to work correctly, all of your transactions need to be categorised. Missing categories in the 'Source Table' show a blank, and blanks aren't picked up by the formulas used in this sheet.

4.   The Date Ranges tab is another lookup table which the report uses to determine which transactions you are interested in from your Source Table. You don't need to change anything on this tab - all of the dates are data driven; they use the =today() and other built-in Excel functions to update automatically.

5.   The 'Report' tab is sort of like the dashboard. It's the basic overview of your finances, listing your Total Income and Expenses by Category and your Net Income for the period. The 'Report 2' tab is a bar chart of the Expense categories for the period you select.

But first, go back to the 'Report' tab and select a time period by clicking on the cell H1. A small drop-down arrow appears to the right - click this and you can select any of the periods, just the same as the Pocketbook web app allows. Selecting this tells the 'Source Data' tab the date range you are interested in. If you want a custom date range, firstly enter the 'date from' and 'date to' (inclusive) in the Custom Date Range box in K9 and K10. After this, go to the drop-down and select 'Custom Date Range' at the bottom of the list.

Now for the piece de resistance - click the 'Refresh PivotTable Data'. This runs the macro we talked about earlier. Essentially it refreshes the data source that the PivotTables are based on, giving the PivotTables the most up-to-date data based on your date range selection. It then unfilters the PivotTable (allowing everything to be populated) and then filters out any categories that have a zero total value.

All of the graphs are PivotCharts based on the PivotTables, and automatically populate after you click the 'Refresh PivotTable Data' button.

From there, you can easily print your report. I hope other Pocketbook users find this useful.

 Feedback? Questions? Got an improvement you'd like to suggest? Let me know in the comments!