There can be multiple data formats for reconciliation, so how do you work around it?

Those who have been following this series of blogs will know the benefits of financial reconciliation and the problem with data reconciliation software. Yet, one thing that also needs to be said is that bank and broker data reconciliation is not always a straight-forward affair. Data formats for reconciliations can be manifold, and off-the-shelf software can often only deal with either one format or one source at a time.


That means that most reconciliation software out there is not a solution at all. So what are you meant to do?

Understanding your needs.

In order to find a good solution, you must first figure out what your needs are. And, as a company, what we have noticed is that it’s the many data formats for reconciliation that leave most businesses baffled.

So, in other words, what the solution needs to do is be able to download, transform, reconcile, and report data from any source and in any format they may come in.

But what data formats for reconciliation are there and how do most companies currently go about reconciling them?

We’re glad you asked!

Email: Bank and broker statements can often come in the shape of attachments, or else in the actual body of the email. Here, the attachment may be in the form of a CSV text file, an EXCEL sheet, a PDF, structured XML, or even an image such as JPEG. In each case, someone will have to open the email, download the file, and then either manually reconcile it with internal ledgers or use an OCR. If that sounds like a lot of work, it is because it is so.

Files via SFTP: Secure File Transfer Protocol is a network protocol that allows for files to be transferred, accessed, and managed safely. It also improves efficiency, as it has functions that can be used to schedule tasks and uploads. Yet this leaves the receivers in the same position as e-mail: with more work to do.

Web Portal: Some banks and brokers have now created web pages that clients can log onto to access their statements. This, however, means that you have to open multiple pages, log into each one separately, download any relevant statements, and then place and convert the fields to meet your needs.

API: Application Programming Interface is a system by which your statements can be presented to you in a more visual manner. This is great compared to the ones previously listed, as it allows you to query your data directly. But your end of the API is not usually provided by the bank or broker. Instead, it requires you to have the coding ability needed to develop it specifically.

FIX: Financial Information eXchange gives brokers and banks the ability to exchange information with you in real-time, no matter where you or they are based. This is, once again, a better alternative to e-mail or SFTP, but it does require the company to employ a FIX expert to keep things ticking over. That, as you can assume, does not come cheap.

OCR: When you receive statements in the shape of an image of some kind, then you have two options: you can have someone check every line manually, or you can use Optical Character Recognition to transcribe it into something you can use. Either way, it’s a lot of work and the report must be made by a human or extra software .

Live Web Data: Some statements come in the shape of data that is displayed live on a website for a certain amount of time. This means that someone must collect the information when it goes live, which can even be late at night, or else have a specific application that is designed to do it on your behalf. These applications, however, are usually different to those that can read information from the formats mentioned above.


So, is there one piece of software that can do all this?

Yes, there is – and you won’t even need to get someone in-house to code or maintain it. We’ll tell you how AUTOMAN can help you reconcile bank and broker statements.