If you’re new to accounting software, you may hear terms like “automated bank feed” or “add-on” and think that all you need to do is turn that “automagical” switch on, sit back and relax your payment data be entered into your online accounting software.
Find out what payment data you need and where it is
One of the first issues you may run into when automating the entry of your payment data is that it resides in multiple places:
- Your online accounting software
- Your bank
- Your payment / e-commerce system
Another thing to keep in mind is all the pieces you are looking for:
- Date
- Where the money was deposited to
- The amount of money
- A description
- What account(s) to attribute the transaction to (like an income account and a merchant transaction fee expense account)
- What taxes were charged
- The Invoice # so that the payment can be applied
- Whether the transaction should create a new customer sales receipt
- Whether the transaction is part of a batch payment
- A class or tracking code
Import your data
In an ideal world, the data from your bank and payment / e-commerce system will be brought in via an API.
Bring data in via an API
API stands for Application Programming Interface. If your online accounting software has an API, it can exchange data back and forth with other software, whether this be a third-party app or a custom app you’ve created.
An example of an API in use is your online accounting software’s “bank feed”. Every hour or every day, it checks with your bank to see if there are any new transactions. If there are, these transactions are imported. Setting up a bank feed is usually an easy way to get payment data imported.
But as mentioned earlier, sometimes you may need more data than what is found in your bank, like when you use an e-commerce / payment system. While it’s standard for online accounting software to have bank feeds, it’s hit and miss that you’ll find an add-on that connects your software with your e-commerce / payment system.
If you are able to find an add-on that allows that transfer of data between the two systems, setting it up is fairly easy. Generally you would need to authorize the data exchange (usually by entering your login credentials), set your import / export options, and then let it run.
If you’re not so lucky as to have an add-on between the two systems, it is possible to build a custom app if they both have an API. For a lot of businesses, creating a custom app isn’t possible (whether due to technical or financial constraints).
Bring in data via a spreadsheet or data file
Some online accounting software, whether natively or via a third-party add-on, allow you to import spreadsheets (CSV or
However, in the majority of online accounting software, importing exported spreadsheet data from an e-commere / payment system is usually not natively possible and requires an add-on.
While manually importing data is not as simple as using an API, it’s better than manually typing in that data.
Automating data processing using rules
After you bring in your data, some rules may need to be set up in order to do things like automatically match payments to invoices or generate customer sales receipts.
Some online accounting software has internal automation rules to help you along, others allow you to create rules for processing these transactions. For example, you may be be able to create a rule where you specify that all payments automatically attribute sales to a specific income account and have a certain % attributed to a merchant fees expense account.
If you have predictable sales types, rule creation can work quite well.
Barriers to payment data automation nirvana
There are various reasons why getting your payment data into your online accounting software isn’t as “automagical” as you’d like it to be. Here are a few things your online accounting software may not be able to do:
It can’t connect to your e-commerce or payment system via an API
This can simply be because an app to connect your e-commerce / payment system doesn’t exist. It can also be because the API only allows access to certain types of data. When I first heard about API’s, I assumed that if the software has an API, I would be able to access all the data. That’s often not the case.
It can’t access a batch summary of transactions
Most e-commerce / payment systems allow you to only import individual transactions as opposed to a batch summary. This can be an issue if all payments for a day are batched together into a single payment. For example, if you receive 5 payments in one day and only receive a single payment into your bank account, not 5. In this example, those 5 imported sales need to be matched to a single bank deposit.
Your software many only allow certain types of transactions / scenarios when dealing with imported software
Most software will allow you to create a new invoice or sales receipt from imported data. However, it’s a lot harder to use imported data to create payments for invoices.
Matching the data between systems may not be possible
If the software doesn’t share some type of unique identifier, like an invoice number, customer name, or transaction ID, matching the data between the two systems may not be possible.
You can end up with missing or duplicate data
How do you know if all your transactions were imported successfully? How do you know if there are duplicates? You really don’t know unless you verify / reconcile the data between the two systems, which is a manual process.
Importing all transactions may not be desired
If you have hundreds of transactions a day, importing every transaction may not be wanted (too much information) or may not be possible (because it can’t handle the volume of data). As mentioned earlier, batching transactions together automatically isn’t usually possible, so importing every single transaction may be too much for your software to handle.
Manually imported data files need to be formatted properly
If you are manually exporting data (like a CSV or Excel file) from somewhere, the data needs to be kept in a completely structured way before being imported. There is very little room for error in your spreadsheets (this means no missing data, incorrect numbers, extra columns, etc…). This most likely means massaging the data you export from a payment / e-commerce system so that it fits the formatting requirements set out by your software. Usually this is a manual process that needs to be repeated every import, but if the exported data is structured enough, sometimes a formatting template can be set up to adjust your data.
It doesn’t have the ability to automatically match payments to invoices
Matching rules usually can’t handle a situation where a single payment is made for multiple outstanding invoices. They also have a hard time dealing with transactions that have the exact same $ amounts, since $ amounts are one of the unique identifiers used when matching. This can mean payments are attributed to the wrong customer.
There’s not enough information
Sometimes there’s not enough data to automatically create a transaction, like when your software wants to create a sales receipt from payment data, but it doesn’t know what taxes were charged or which income account to attribute the money to.
There’s too much complexity
If your sales can be attributed to multiple income accounts or have varying sales taxes used, this can’t easily be automated (unless there’s something unique about the transaction so that a rule can be made).
Conclusion
While it would be lovely to automate the entry of your payment data into your online accounting software, there are many barriers to truly 100% automation. Look for online accounting software and payment / e-commerce systems that can work together and be automated as much as possible. Any time you go beyond creating a simple sales receipt with a single income account and
Here’s a checklist of things to look for when trying to reach payment data automation nirvana:
- Can you exchange data between your online accounting software / bank / payment / e-commerce system using an API? This is the best case scenario. If not, can data can be exported / imported using a spreadsheet / data file?
- Between all your sources of data, is there enough information to automate the process? Some manual entry of data may be needed if there’s not.
- Can rules be created and modified to automate the entry, matching, and merging of data?
- For sales receipts, can multiple accounts and sales taxes be used? Can expenses like merchant fees or shipping costs be entered as well?
- For payments, how good is the matching between them and their invoices? Is there an easy way to attribute a single payment to multiple invoices?
- Is there an easy way to verify that all data is entered correctly and there’s no missing or duplicate data?
- Do you need to enter every single transaction or can you batch transactions together (like a daily sales summary)?