how to export Quickbooks Memorized Transactions

For years my Memorized Transactions has represented the consistent income flow for my business.  And for years, Quickbooks has not provided a way to export memorized transactions into Excel for analysis and charting.

I spent some time messing with ODBC systems trying to hook directly into the Quickbooks database in order to see if I could get at them with SQL.  I didn’t spend much time on this, as I had pretty serious issues getting the Flexquarters ODBC driver to work at all in Windows 8.1.  I gave up on that pretty quickly, and stewed on the problem for a while.

Then I thought:  Why not OCR?

I’ve managed to successfully get my hundreds of memorized transactions into Excel.  Here’s what you’ll need:

  1. A simple image editor (MSPaint will do fine)
  2. Acrobat Pro
  3. Excel, of course.

How to do it:

  1. Take screenshots of your memorized transactions.  Save as PNG.  Be sure to do multiple shots and crop appropriately to avoid duplicates.  Crop out all of the extraneous stuff, just keep the inner window with the data you actually want.
  2. Open Acrobat Pro, and under Preferences > Convert to PDF > PNG, edit the Settings and set the Color Compression quality to Max.  This is to prevent any compression artifacts from making your PNG difficult to OCR by preserving its best quality when imported into Acrobat.
  3. Create a new PDF from File, starting with one of your PNGs.
  4. Single-left click the image in the PDF, and then right-click the image, and click “Recognize Text using OCR”.
  5. You will now have a PDF that contains actual text for your transactions.  Now here I tried a few things – save as Text, save as Excel, but those will not give the best result.  Save it as a Word document.
  6. Open the word document in Word, and highlight everything and copy/paste it into Excel.  The Word export from PDF will preserve the table-like layout of things, and copying that into Excel will ensure that things are preserved into cells.
  7. Repeat the steps above for the other screenshots you made.
  8. You’re not quite done yet.  The OCR isn’t perfect, and some data will be chewed up a little.  Double-zeros will be double-character-Os.  Fix those with a search/replace.  The space between the Amount and Frequency, such as “$10.00 Monthly” might have a “j” in the space, because of the line being recognized as a character.  A few search and replace techniques should take care of that.
  9. Typically you will have many transactions that are an assortment of Monthly, Annually, Twice a Year, or Quarterly.  A good first task is to calculate monthly income.  A few clever formulas will solve that:

B2 cell contents:  120.00 Annually
Result cell contents:  120.00
The NUMBERVALUE() bit will ensure that the final result is actually a number so that any calculations done later do not fail.  This caught me by giving me false totals until I added that function.

=RIGHT(B2, LEN(B2)-FIND(” “,B2,1))
This will pluck the word “Annually” or similar out of the field and put it in its own cell.  This is also good for verifying that you cleaned up all the spaces correctly
B2 cell contents:  120.00 Annually
Result cell contents:  Annually

=IF(D2=”Annually”,C2/12,IF(D2=”Twice a year”,C2/2,IF(D2=”Quarterly”,C2/4,NUMBERVALUE(C2))))
This will calculate the monthly Amount factoring in the Frequency properly.
B2 cell contents:  120.00 Annually
C2 cell contents:  Annually (created using first formula)
Result cell contents:  10

That’s it.  You now have all your memorized transactions converted to monthly income in Excel.  I know it’s not perfect, but it works.  The Excel cleanup you have to do may be a little annoying if you have more than a hundred transactions, but I’d suggest this for even up to 1000 of them.  Also, it may be annoying, but it sure beats manually data-entering them from scratch.

You really don’t want to do all this twice, so try to manually update your spreadsheet anytime you update your memorized transactions.