Script to generate AppleScript for entering transactions into Quicken for Mac

CSVtoAppleScriptforQuicken.py

# Script to turn a CSV into input for Quicken for Mac
# 2021-06-03 - joe at emenaker.com

# Background: Quicken can download transactions directly from your bank, but
# there's usually a limit to how far back the data goes. If you wait more
# than several months between downloads, you'll end up with a gap in
# your transactions, which you would need to fill by hand-entering
# transactions from printed or PDF statements from your bank.
#
# If you have access to the PDF statements, it's fairly easy to cut/paste
# the transaction data into a spreadsheet and save it as a CSV, but
# Quicken doesn't like importing CSV. There exist tooks to convert CSV
# to formats Quicken _does_ like (like QIC), but Quicken only likes to
# import those into a new register, not adding individual transactions
# into an existing register.
#
# One solution (used here) is to use the CSV data to actually simulate
# user keystrokes, entering the data. Of course, this is OS and
# application dependent. Older versions of Quicken for Mac used to
# support Automator tasks, but I hear this no longer works, so the
# input has to be done by using the "System Events" application to just
# send keystrokes to whichever application is active.

# Operation: This script turns a CSV file into an Applescript which
# activates Quicken and then starts blindly sending keystrokes. Timing
# is a bit of an issue, here, because Quicken needs time to prepare a
# new transaction and to advance to other fields when a <tab> is sent.
# Otherwise, the focus won't advance to the correct field and things
# like payment_amount will get entered into the "Memo" or "Category"
# field. TEST THIS SCRIPT WITH A SMALL NUMBER OF TRANSACTIONS, FIRST,
# to make sure that your delays are long enough.

# Usage: This script, as written, assumes that the CSV is a file
# named Transactions.csv in the format of:
#    dd/mm/yyyy,<payee>,<amount>
# Run: ./CSVtoApplescriptforQuicken.py
# and it will just output a list of Applescript commands to the console.
# Copy these lines to the clipboard.
# Run Automator on your Mac
# Create a new Workflow
# Add a "Run AppleScript" task
# Erase the starter script provided and paste in the copied lines
#   from the console
# Make sure that Quicken is running and you have selected the
#   ledger you want to add the transactions to
# Click the "Run" button in Automator
# Automator should move Quicken to the foreground and start
#   entering transactions.

# Tips:
# Make sure the dates in your CSV are correct. Bank statements
# typically just have "mm/dd" format, and pasting these lines
# into a spreadsheet will cause these dates to be assumed to be the
# CURRENT year. Use your spreadsheet skills to fix the year to an
# earlier one if you need.
#
# Do NOT click on any other applications while this is running
# (or else Automator will just start sending the keystrokes to
# that application). You might want to consider disabling your
# network connection to prevent incoming Zoom/Skype/etc calls
# or other popups while this is happening.

f = open("Transactions.csv","r")
lines = f.readlines()
f.close()

print("on run {input, parameters}")
print("tell application \"Quicken\" to activate")
print("tell application \"System Events\"")

for line in lines:
        (date,payee,amount) = line.rstrip().split(",")
        (month,day,year) = date.split("/")
        # Start a new entry, and wait for Quicken to have it ready
        print("keystroke \"n\" using command down")
        print("delay 2")
        # Enter the date
        print("keystroke \"{0}\"".format(month))
        print("keystroke \"/\"")
        print("keystroke \"{0}\"".format(day))
        print("keystroke \"/\"")
        print("keystroke \"{0}\"".format(year))
        print("delay 1")
        # Enter payee
        print("keystroke tab")
        print("delay 1")
        print("keystroke \"{0}\"".format(payee))
        print("delay 1")
        # Enter category
        print("keystroke tab")
        print("delay 1")
        print("keystroke \"Uncategorized\"")
        print("delay 1")
        # Enter amount
        print("keystroke tab")
        print("delay 1")
        print("keystroke \"{0}\"".format(amount))
        print("delay 1")
        print("keystroke return")
        print("delay 1")

print("end tell")
print("return input")
print("end run")