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!

Results from Media Blackout May

How time flies. I said that I'd make an update in June on how Media Blackout May went - and it's now halfway through August... Oh well, better late than never I suppose. To be fair though, I didn't jump straight back into consuming news and social media as soon as June rolled around, I have actually maintained the media blackout for the intervening months. And really, that was sort of the point of this experiment - to change behaviour and habits. So how did it go?

Initially, it was difficult. If your go-to habit when there's not much happening is to whip out your phone and check Facebook, then suddenly you're left with nothing. If you watch or read the news over breakfast, as I did for a while, then that's no longer an option. It was interesting to see just how many times you would find yourself in that situation each day - just kind of staring blankly, asking yourself, 'what can I do instead?'. But after a while, probably a couple of weeks or so, these moments became an opportunity to find some clarity and focus. To think about what I was doing beforehand and what I was going to do next, or noticing that I needed a break from whatever I was doing.

Have I felt out of the loop? Not really. In fact, I feel like I've side-stepped a number of things that might have made me angry or upset. Also, because I haven't read everyone else's analysis of an event, I'm free to do my own. In a world inundated with information, it's very easy to get bogged down and very easy to rely on others to form your opinion for you. The 'low information diet' helps with this; your brain has less stuff to sort through and process, and once it has, it can spend more time connecting and creating. The more you spend time away from these sorts of time sinks, the more you realise how unnecessary they are to your day-to-day life and the more time you can spend doing things you enjoy. In the last couple of months, I've spent a lot more time reading, doing band stuff and writing. It's been nice.

Again, I want to stress that I don't think that news and social media are useless, I just think that I needed to reprioritise their importance in my daily routine. More often than not, they'd just become a  habit - something to occupy a few minutes of time between other things. Which I think is a comment on a wider trend, namely our compulsion to always be entertained and always be doing things.

Short Story: Pensioner Zero

Hey all, this post is a quick revisit back to a short story I wrote a while ago. For a little background, I wrote this while I was working for Aldi while I was looking for a professional job in Canberra. I may or may not have been bitter and cynical at the time of writing. Enjoy!

Pensioner Zero

Like zombies they gather, in the early morning - haunting carparks with Zimmer frames and canes. Crowding around the clinical tempered glass of the protective electronic double sliding doors that separate us. Waiting for the signal to be given, waiting for the mechanism to unlock, waiting to seep through the crystalline barriers to shuffle idly, mindlessly back and forth through the aisles.

From behind the cage they exertedly push, like Sisyphus rolling the stone, they peruse this week's specials at a lurching amble.

"My gosh, that's cheap." they remark regarding some low-quality, built-to-a-price-by-the-lowest-bidder, piece of consumer electronics, before registering a complaint about the lacklustre workmanship.

"They just don't make 'em like they used to," they croak to each other in raspy tones, "everything is made in bloody China now". They express their myopic perspective; vision which cannot be improved in clarity with the best of lenses, served with a side of borderline racist undertones.

Shovelling canned goods and other flotsam and jetsam into their metallic nets, they march inevitably toward the counter. They unload their items onto the belt at a painfully slow pace; a speed that makes human evolution look as though it happened overnight. The red second hand clicks loudly. Much to the annoyance of the cashier, it runs a full revolution. Then another. And another. 

Their backs are weak, their knees stiff, their bones brittle. They just don't make 'em like they used to. They exhaustedly jostle the wheeled basket into the receiving position, an elegant manoeuvre in the supermarket leagues. They nod, as if in slow motion, toward the cashier indicating their readiness to make the play.

Without hesitation, the cashier proceeds to fire items past the scanner, only breaking to demonstrate a level of dexterity unseen by their generation. Groceries pile up faster than a freeway in a blizzard with zero visibility. The scanner sounds in a chorus of rapid-fire bleeps. The elderly couple cannot abate the onslaught.

As if the experience was over before it began, the total amount gleans toward the couple in a dull green, dot-matrix font, the likes of which haven't been seen since the early nineties.

"Cheaper than I thought" they crow at each other. 

Turning their beaks toward the cashier, they proceed to dig through knitted cardigan pockets searching for their pension money. The second hand completes another lap. Like archaeologists they manage to unearth a wrinkled fifty dollar note, buried beneath a layer of glasses cases, used tissues and other debris. Instantaneously the cashier makes their change, and offers a polite but trite farewell, and turns to the growing line of other grumpy old bitties.

"Where are our bags? Aren't you going to pack these?"

Remnants of a dying epoch, fossilised by the inevitable march forward of productivity.