As bookkeepers and accountants know, multicurrency transactions cannot be lump summed into a single currency. Instead, we must separate each transaction out of the report then record these transactions into the proper bank account or PayPal account in our case. If your base currency is Canadian and you have to pay vendors in USD, you must convert your Canadian currency into American and then pay the vendor in USD. You might say to yourself well that seems obvious, and I would agree, however the integration with PayPal, and Quick books online does not take this into account. The available integration on Quick books site only allows you to link the currency to ONE bank account. Therefore all transactions are lumped into one currency… Well, that is wrong…
Not satisfied with having an integration which didn’t work. I decided to look into the applications available from third-party services such as Zapier. While they had some integrations, which were more useful than the regular add a bank account that QuickBooks offered, it was still incomplete as you are only able to track your sales from PayPal. Continuing to look, I still could not find a proper automated solution; this was shocking to me. Why is there not a solution to have multi-currency PayPal accounts separate and recorded in different GL accounts and pulled into the Quick books online banking transaction module (which is amazing – the tool makes reconciling bank accounts super easy).
Unwilling to accept this as an answer, I went ahead and began to look for solutions to completely automate this activity. This first issue was getting the transactions from PayPal in a useful format. First thing I had to do was find the actual report that would provide me the information I needed. Navigating through PayPal’s report tabs, I discovered the Activity download merchant page https://business.paypal.com/merchantdata/reportHome – this report had all the necessary information I needed. Luckily for us, the report is in a great format to work with CSV. However, this report shows all transactions from the day; it does not break out if you had a purchase in Canadian or American or Euro. Useful report as it has all the required information, but it still needs refinement. Deciding that I wanted to have my accounting pull reports each day as I would like to have my accounting records up to date and as close to real time as possible, I wanted to figure out a way to extract this report each day.
At this point, I did not know to program but was determined to figure out a solution to my problem. As part of my search, I discovered a coder by the name of Al Sweigart who created a course named “Automate the Boring Stuff with Python Programming” (you can find the link here: https://www.udemy.com/automate/ – I have no affiliation with Al Sweigart) this was the learning tool I used to start my coding career. The course gave me the basic skills required to write and navigate websites. However, it was not the only resource I needed to create my first script.
My problem: Getting the CSV report from PayPal every day, and I was determined to figure out how to do this automatically. If you go ahead and take Al’s course, you will learn about the basic features of Python. As such, he utilizes the Selenium python library, which allows us to tell our computer how to navigate webpages. Super cool, right?
The Selenium web browser (with Firefox or Chrome extension [exe file]) allows us to open up a new webpage using the Python command line. After opening up our driver, we need to tell the driver which URL to go to. Using the below code, we can sign into PayPal’s Canadian site. (Note we are importing our credentials from another python file to secure our password.)
I’m not going to go into details about how to identify webpage elements, as there are numerous sources on YouTube and the internet explaining how to identify these elements. One piece of advice when trying to figure out how to select the element you are looking for – open your command prompt and test your code through it to determine if you have identified the element correctly, then add it to your IDE or text editor. It will save you a lot of time versus writing it all in an IDE/text editor like Sublime Text.
password = PP_Creds.login['consumer_secret']
url = 'https://www.paypal.com/ca/signin'
#To Login to PayPal
Cool, now we are logged into PayPal. Now we have to figure out how to navigate to the reports page. Let’s grab this report, well it’s not as easy as just navigating webpages. You will likely run into latency problems or slow responses from the server, whether it is on your side or their side. It doesn’t matter, but we need to build a system to gracefully fail or repeat a task instead of having our script abruptly stop due to not finding the required element. Failure occurs when we want to use a “Try” exemption, which allows the robot to look at the webpage and determine if that element is present. If it cannot locate the element, it will “raise” an exemption which you have expressed, in other words instead of our script failing our script will say it cannot find the element and will skip this requirement. As such, I have built in an additional buffer period of 15 seconds (time.sleep(15)) and try again to click on the element. Cool, this seemed to have fixed my loading issues.
except Exception as d:
except Exception as f:
Not only do external forces such as lag or server delays cause your script to fail, but also websites will attempt to trip you up to stop you from running robots on their website, as such we need to be creative in the ways we select their elements. For example, PayPal uses an element id called “react-datepicker-dropdown-“[xxxxx], where the element has a changing suffix. Well, how do we deal with this? Well, first of all, we have to figure out if there are multiple elements which have similar id tags. If there are additional id tags, we have to be able to isolate the specific element we want. Luckily for us, we do not have multiple tags with the same value. Great this means we can use the XPath function ‘//*[contains(@id, “react-datepicker-dropdown”)]/button’. If you are familiar with HTML at all, you will recognize that typically the id tag is followed by an equals sign, however, when you use the contains command you use a comma, not an equals sign. The comma allows us to identify the drop-down menu of the randomly changing suffix.
The third challenge I ran into was downloading the report. When you go ahead and download the CSV file, PayPal sometimes has a download queue. Which means that you will not get your file right away, well that’s an issue if you are trying to click download the file and it isn’t available isn’t it. So what do we do? Well, we need a loop of some sort, I used the following loop to solve the problem.
We are looking for a button which says Download. The download links name changes when it is available to be downloaded. If it is in the queue, it says unavailable; this means that if we went ahead and strip the text of the XPath, we will receive a string or a value of “unavailable.” This script allows us to create an infinite loop until that element has changed to “Download,” as such we use the following script.
The if statement allows us to evaluate if the element says Download or not if the value is not “Download,” then we wait 90 seconds, refresh the page, wait another 15 seconds, navigate to the same report page, wait another 15 seconds and then re-evaluate what the element says. If it still does not say download, we will repeat the steps until our value says download.
if value != "Download":
while value != "Download":
value = driver.find_element_by_xpath('//*[@id="pastHistory"]/table/tbody/tr/td').text.strip()
Finally, we go ahead and download the report.