Automate Uploading PNC Virtual Wallet Statements into Evernote with Hazel

I’ve been on a bit of a productivity kick recently.  My newfound passion with productivity was a direct result of a guy I work with (thanks @DigitalPeterson) hassling me about dusting off OmniFocus and trying it again.  Most of my success can be attributed to him, but also to the guys over at Asian Efficiency.  They provide an amazing 10-part OmniFocus set of tutorials that I highly recommend.  If you want to get started with OmniFocus faster, then I’d also recommend their Asian Efficiency OmniFocus Premium Posts.

Once you start down the path to improving your personal productivity, you’ll eventually get to a point where you want to start branching out and looking into additional tools, going paperless, and automation.

This article addresses a specific issue I had with automating the download of my bill and account statements into Evernote.  Normally, most of my accounts automatically download into Evernote through the use of FileThis Fetch (if you haven’t taken a look at it, go now, I’ll wait).  However, PNC Bank makes integrating their Virtual Wallet accounts extremely difficult.  To be honest, I haven’t found (m)any automated services that integrate into PNC Virtual Wallet.

To solve my problem, I integrated Hazel, a shell command, Evernote, some good advice, and ended up with a working solution that automatically takes my downloaded statements from Spend, Reserve, and Growth accounts and gets them into Evernote with almost no effort on my part.

The workflow you’ll have after completing the instructions in this article will be:

  1. Log into PNC Bank Virtual Wallet
  2. Download a statement (Spend, Reserve, or Growth)
  3. Hazel will automatically recognize the statement’s account (using PDFtoText)
  4. Hazel will automatically  determine the statement’s date (using my nifty shell command)
  5. Hazel will automatically rename the file for the proper account and statement date
  6. Hazel will automatically create a new note in Evernote and place it in the correct Notebook with the statement’s date as the creation date
  7. Hazel will then automatically delete the note from the Downloads folder

So, the only action you’ll have to take is logging in and downloading the statement.  Hazel, AppleScript, PDFtoText, and the shell script will take it from there.

Getting Started

The bulk of this idea came from an outstanding article on Automated bank statement filing with Hazel over at The Savvy Technologist.  He got me started down the path with using Hazel to solve this problem and I adapted some of his code for my solution.

This solution relies on having PDFtoText (part of the Xpdf open source package) installed on your Macintosh.  You can download a pre-built installer here.  Once you have it installed we’ll move to the Hazel portion of our workflow.

Downloading the Statement

This is the easy part.  Log into your PNC Virtual Wallet account and download a statement.  Leave it in your Downloads folder for the rest of the automation workflow to work its magic.

Hazel Rules

Hazel is a remarkable tool.  Its basic function is to watch a folder and when a trigger condition happens, fire an action (or actions).  That simple premise provides an amazing amount of power.

Unfortunately, I had to write a separate rule for each of my Virtual Wallet accounts.  Each rule is different only in the name of the account being processed, for the purposes of this article I’m only going to build the Rule for my Virtual Wallet Spend account.  Just duplicate the rule and change “Spend” to “Reserve” or “Growth” for your other Rules.

The Trigger

The first part of the Hazel rule is called the Trigger.  This is how Hazel knows to fire the Action.  Hazel has a built-in ability to read inside PDF files, which we use for the Trigger:

HazelPNCTrigger

This Trigger is defined on the Downloads folder and will only fire if the file found there matches both conditions:

  1. Kind is a PDF file
  2. Contents of the PDF file contains the phrase “Virtual Wallet With Performance Spend”

I have a PNC Virtual Wallet Performance account, if you did not change your basic account to a Performance account, then you just have to remove “With Performance” from the Trigger condition.  This should be the name of the account you see at the top of your statements.

The Actions

The actions are where all of the magic occurs.  There are four actions to this Recipe:

HazelPNCActions

The first Action runs an embedded AppleScript.  That script also sets three Hazel variables:  stmtYear, stmtMonth, and stmtDay.  These will be used in the next Action.  Here is what that Action looks like in Hazel:

HazelPNCAction1

Some explanation of this AppleScript.  The first line just sets itemPath to the full path of theFile (an AppleScript variable that provides the name of the file that Hazel has matched).  We’ll need the full path for the next step.  The second line of the script determines the account statement date.  This is the step we need to use PDFtoText in order to read the text string in the PDF file.  The final line of the script returns Hazel tokens representing the three variables we defined in the Action.

The full script can be copied from here, just make sure to also define the three variables where shown:

1 2 3

set itemPath to quoted form of POSIX path of theFile set dateString to do shell script "/usr/local/bin/pdftotext " & itemPath & " - | grep -E [0-9]{2}/[0-9]{2}/[0-9]{4} | awk 'NR1 {print $3}'" return {hazelExportTokens:{stmtMonth:word 1 of dateString, stmtDay:word 2 of dateString, stmtYear:word 3 of dateString}}

The third action renames the generic “StatementPDFServlet.pdf” (the name PNCBank gives to all of the statement downloads) to something more useful.  The rename Action uses the three Hazel tokens we defined earlier to rename the file into the format:  “Virtual Wallet Spend – ” followed by the statement date in YYYYMMDD format (for easier sorting). You should be able to add the tokens to the rename Action like below.

HazelPNCAction2

The third Action statement is our second AppleScript and has Hazel upload the file to Evernote, set the creation date, and store it in a Notebook named after the name of the account.

HazelPNCAction3

You’ll recognize the first two lines of code, we used them in the first AppleScript.  Unfortunately, I need the date again and I’m not sure how to reuse them down in this script, so I run the shell script again and parse the date.  I then tell Evernote to create a new note with the file we renamed, place it in the notebook called “PNC Virtual Wallet – Spend” and set the created date to the date of the statement we parsed earlier.

The full script can be copied from here:

1 2 3 4 5

set itemPath to quoted form of POSIX path of theFile set dateString to do shell script "/usr/local/bin/pdftotext " & itemPath & " - | grep -E [0-9]{2}/[0-9]{2}/[0-9]{4} | awk 'NR1 {print $3}'" tell application "Evernote" create note from file theFile notebook "PNC Virtual Wallet - Spend" created (date dateString) end tell

The final Action just moves the original file from the Downloads folder into the Trash, completing the Recipe.

Improvements

If you don’t mind having really long Notebook names in Evernote, you could easily combine the three Recipes into one, expanding the variables to include the name of the account and then using that elsewhere when naming the file and setting the Notebook.  Of course, automating the actual downloading of the statements on a timed basis using something like Lingon would be ideal.  Previously, PNCBank only supported the Virtual Wallet online site within Flash, but recently they adopted HTML 5 (in beta), which gives some opportunities for scripting.

applescript automation evernote hazel howto paperless Productivity shellscript
Tweet Post Share Update Email RSS

Leader, Mentor, Challenger, Educator, Network Engineer, System Administrator, Developer, Hacker, Writer, Diver, and Technology Explorer.