Skip to main content

quickbooks_gui_automation.py

import csv
import datetime
import openpyxl
import pyautogui

class CustomerListParser(object):

    def __init__(self):
        self.customer_dict = {}
        self._FIRST_CUSTOMER_ROW = 4
        self._CUSTOMER_NAME_COLUMN = 1
        self._EMAIL_COLUMN = 2
        self._PAYMENT_EMAIL_COLUMN = 3
        self.customer_sheet = None

    def parse_customer_list(self, file_name):
        customer_wb = openpyxl.load_workbook(file_name)
        self.customer_sheet = customer_wb.active
        for row in range(self._FIRST_CUSTOMER_ROW, self.customer_sheet.max_row + 1):
            self.parse_customer_entry(row)

        return self.customer_dict

    def parse_customer_entry(self, row):
        customer_pay_email = self.customer_sheet.cell(row=row, column=self._PAYMENT_EMAIL_COLUMN).value
        customer_name = self.customer_sheet.cell(row=row, column=self._CUSTOMER_NAME_COLUMN).value
        if customer_pay_email:
            self.customer_dict[customer_pay_email.lower()] = customer_name

        customer_email = self.customer_sheet.cell(row=row, column=self._EMAIL_COLUMN).value
        if customer_email:
            self.customer_dict.setdefault(customer_email, customer_name)


class PaymentListParser(object):

    def __init__(self, customer_dict):
        self.customer_dict = customer_dict
        self._TRANS_ID_COLUMN = 1
        self._AMOUNT_COLUMN = 2
        self._FEE_COLUMN = 4
        self._NET_AMOUNT_COLUMN = 5
        self._EMAIL_COLUMN = 8
        self._NAME_COLUMN = 9
        self._DATE_COLUMN = 10
        self._PAYMENT_METHOD_COLUMN = 15
        self._SHORT_ID_LENGTH = 11
        self._DATE_LENGTH = 10
        self.unidentified_customers = set()
        self.payments = []

    def parse_payment_list(self, file_name):
        payment_list_file = open(file_name, 'r')
        payment_list_reader = csv.reader(payment_list_file)
        for row in payment_list_reader:
            if payment_list_reader.line_num <= 1:
                continue
            pay_email = row[self._EMAIL_COLUMN].lower()
            if pay_email in self.customer_dict:
                customer_name = self.customer_dict[pay_email]
                payment_info = self.parse_payment(row)
                payment_info.customer_name = customer_name
                self.payments.append(payment_info)
            else:
                self.unidentified_customers.add(pay_email + ', ' + row[self._NAME_COLUMN])

        payment_list_file.close()

    def parse_payment(self, payment_row):
        date_in_str = payment_row[self._DATE_COLUMN][:10]
        date = datetime.datetime.strptime(date_in_str, '%Y-%m-%d')
        date_out_str = date.strftime('%m/%d/%y')
        amount = payment_row[self._AMOUNT_COLUMN]
        payment_method = payment_row[self._PAYMENT_METHOD_COLUMN]
        trans_id = payment_row[self._TRANS_ID_COLUMN]
        short_id_start = len(trans_id) - self._SHORT_ID_LENGTH
        short_id = trans_id[short_id_start:]
        fee = payment_row[self._FEE_COLUMN]
        net_amount = payment_row[self._NET_AMOUNT_COLUMN]
        return PaymentInfo(date_out_str, amount, payment_method, short_id, fee, net_amount)

    def get_payments(self):
        return self.payments

    def get_unidentified_customers(self):
        return self.unidentified_customers


class PaymentInfo(object):

    def __init__(self, date, amount, payment_method, trans_id, fee, net_amount):
        self._date = date
        self._amount = amount
        self._payment_method = payment_method
        self._trans_id = trans_id
        self._fee = fee
        self._net_amount = net_amount

    @property
    def customer_name(self):
        return self._customer_name

    @customer_name.setter
    def customer_name(self, value):
        self._customer_name = value

    @property
    def date(self):
        return self._date

    @date.setter
    def date(self, value):
        self._date = value

    @property
    def amount(self):
        return self._amount

    @amount.setter
    def amount(self, value):
        self._amount = value

    @property
    def payment_method(self):
        return self._payment_method

    @payment_method.setter
    def payment_method(self, value):
        self._payment_method = value

    @property
    def trans_id(self):
        return self._trans_id

    @trans_id.setter
    def trans_id(self, value):
        self._trans_id = value

    @property
    def fee(self):
        return self._fee

    @fee.setter
    def fee(self, value):
        self._fee = value

    @property
    def net_amount(self):
        return self._net_amount

    @net_amount.setter
    def net_amount(self, value):
        self._net_amount = value


class PaymentGUIEnterer(object):

    def __init__(self):
        self._QUICK_BOOKS_SIDE_MENU_LOC = (26, 30)
        self._CUSTOMERS_MENU_LOC = (402, 12)
        self._RECEIVE_PAYMENTS_MENU_CMD_LOC = (433, 267)
        self._AUTO_APPLY_BUTTON_LOC = (856, 873)
        self._DISCOUNT_INFO_BUTTON_LOC = (856, 846)
        self._PAYMENT_AMOUNT_LOC = (390, 237)
        self._SAVE_BUTTON_LOC = (1060, 975)
        pyautogui.PAUSE=0.1

    def enter_payment(self, payment_info):
        self.activate_quickbooks()
        self.open_receive_payments_window()
        self.enter_payment_info(payment_info)
        self.apply_payment_to_invoices()
        self.enter_credit_card_fee(payment_info.fee)
        self.subtract_fee_from_payment(payment_info.net_amount)
        self.record_transaction()
        self.close_receive_payments_window()

    def activate_quickbooks(self):
        pyautogui.click(*self._QUICK_BOOKS_SIDE_MENU_LOC)

    def open_receive_payments_window(self):
        pyautogui.click(*self._CUSTOMERS_MENU_LOC)
        pyautogui.click(*self._RECEIVE_PAYMENTS_MENU_CMD_LOC)

    def enter_payment_info(self, payment_info):
        pyautogui.typewrite(payment_info.customer_name)
        pyautogui.typewrite(['tab'])
        pyautogui.typewrite(payment_info.date)
        pyautogui.typewrite(['tab'])
        pyautogui.typewrite(payment_info.amount)
        pyautogui.typewrite(['tab'])
        pyautogui.typewrite(payment_info.payment_method)
        pyautogui.typewrite(['tab'])
        pyautogui.typewrite(payment_info.trans_id)
        pyautogui.typewrite(['tab'])

    def apply_payment_to_invoices(self):
        pyautogui.click(*self._AUTO_APPLY_BUTTON_LOC)

    def enter_credit_card_fee(self, fee):
        pyautogui.click(*self._DISCOUNT_INFO_BUTTON_LOC)
        pyautogui.typewrite(fee)
        pyautogui.typewrite(['enter'])

    def subtract_fee_from_payment(self, net_amount):
        pyautogui.click(*self._PAYMENT_AMOUNT_LOC)
        pyautogui.keyDown('ctrl')
        pyautogui.press('k')
        pyautogui.keyUp('ctrl')
        pyautogui.typewrite(net_amount)

    def record_transaction(self):
        pyautogui.click(*self._SAVE_BUTTON_LOC)

    def close_receive_payments_window(self):
        pyautogui.keyDown('command')
        pyautogui.press('w')
        pyautogui.keyUp('command')


if __name__ == "__main__":
    customer_parser = CustomerListParser()
    customer_dict = customer_parser.parse_customer_list('customer_list.xlsx')

    payment_parser = PaymentListParser(customer_dict)
    payment_parser.parse_payment_list('payments-2017-04-28-065748.csv')

    payment_enterer = PaymentGUIEnterer()
    for payment_info in payment_parser.get_payments():
        payment_enterer.enter_payment(payment_info)

    print '\nUnidentified Customers:\n'
    for customer in payment_parser.get_unidentified_customers():
        print customer

Comments

Popular posts from this blog

When All Else Fails, Use the GUI

In this blog post, I show you how I automated entering credit card transactions in QuickBooks using the PyAutoGUI package for Python. Automating routine tasks is a great way to save yourself a lot of time. Whenever I spend a significant amount of time doing a partiuclar task on a computer, I ask myself if there is a way to automate it. Generally, as long as the input to the task is located on the computer, the answer is yes. The kinds of tasks that evade automation are those that have some kind of physical medium as an imput. While there are technologies that could help automate those tasks, it is much more complicated and difficult to do so. For tasks that can be automated, my favorite language for doing so is Python. Python has a wonderfully readable, expressive syntax, and there are an astoundingly large number of libraries and packages available that make the job of automating jobs much easier. One of my favorite simple examples of how Python saved me a lot of time is when I

The $10,000,000 Question

Neal Ford posed a question on Dave Rael's Developer on Fire podcast. He asked whether if you were offered $10,000,000 on the condition that you could never write another line of code for the rest of your life, would you take it? I'll leave it to you to go listen to the podcast if you want to hear Neal answer his own question, but you can have my answer here. Why Take the Money For me, and probably a lot of other people, the purpose of accepting the money would be to gain freedom. Having a sufficient amount of money with which one could live out the rest of one's life comfortably changes one's perspective and position considerably. With $10,000,000, I would no longer need to work. That's probably obvious, but considering how much of our time is spent working in and on our careers, that's an enormous gain. All of that time comes back to me because I am no longer compelled by necessity to go out and earn a living through labor. The Value of Work The difficu

Books That Have Influenced Me and Why

A mantra that I often repeat to myself is, "Don't abandon the behaviors and habits that made you successful." I believe this trap is actually much easier to fall into than most people realize. You can sometimes observe it in the context of a professional sporting event such as American football. One team might dominate the game, playing exceptionally well for the first three quarters. Then, as they sit with a comfortable lead, you see a shift in their strategy. They start to play more conservatively, running the ball more often than they had. Their defense shifts to a "prevent" formation, designed to emphasize stopping any big plays by the other team while putting less pressure on the short game. The leading team often looks awkward in this mode. They have switched their perspective from that of pursuing victory to that of avoiding defeat. They have stopped executing in the way that gained them the lead in the first place. I have seen more than one game ult