RiskTech Forum

Aspect: Integrating Accounting Systems with ETRM Software

Posted: 3 July 2017  |  Author: Adam Rice  |  Source: Aspect Enterprise Solutions


At ResourceFueled, we are often contracted by a client to implement custom interfaces to meet a company's specific needs. We recently implemented an interface of Aspect's ETRM software into an accounting system. The accounting system in this instance was SunSystems, but it doesn’t make much difference what the accounting system is.

In this case our client had contracted us to build the interface. The most eye-catching part of the interface is the building of the reports that the journal entries consist of. But this happens near the end of the project and, because we have done interfaces before, the writing of these reports are relatively quickly done. Aspect’s excellent script writing functionality that is available to customers makes this a well-defined task which is quite fun to do for those of us who like getting into ‘the zone’. Before writing the reports, there is a lot of analysis, explanation to the client of options, and decision taking to be done.

Customer Databases

Since you don’t want to enter customer details into two systems, we first had to move information from the database which holds data in a detailed form into a database that holds it in a sparse form. In this case, we decided to first implement a semi-automatic process for a few months before launching a completely automatic system. This allowed for the accountants to have the chance to get used to the rhythm of the process of information flowing from their ETRM software to their accounting system. Once the process settled down and everyone was sure that it was working for them, the business got comfortable with running the second stage of the project which was to deliver full automation with web-services, which is what we did next.

Interface Options

Our client asked for an interface to be built for them, but they didn’t necessarily know their processes or what journals need posting. Here are some of the questions we had to address before integrating the ETRM software with the accounting system:

Accounting Policy

Another important consideration is accounting policy. There are three relevant options:

There was a mutual agreement between our team and our client’s team, that posts would occur in SUN using M2M so that the PnL in Aspect and SUN was equal. A reconciliation process was put into place for month-end reports to prove that the profit and positions in each of the two systems agreed.

Completeness, Accuracy and Validity

Once the process was agreed upon, it was time to design the interface.

It was now time to write the reports in Aspect's ETRM software. This is something that can be done fairly quickly in scripts because these things have been written before and most of the tricky problems already have solutions. But it is devilishly difficult to be accurate the first time here and extensive testing had to be done to confirm that the output was correct. The reports that were created needed to be transformed into a format that could be uploaded into the accounting system. We did this using a macro that is available to users as an add-in on everyone’s computer. Users run reports that they want posting in that session. The macro takes each report and sorts and formats it so that it is ready to be uploaded. It also adds journal details to a journal log which records when it was posted, which type of journal it was, which period it covered, and which legal entity etc. It then closed the original report so that it wasn’t posted again. The user just runs the macro/ posts until, one by one, all  journals are complete and reports closed.

Keeping Data Clean

It is important that the data is kept in the trading system in a way that works for accounting. Within the ETRM software, you want to keep all your transaction data at ‘leaf node’ level, make sure reference data is complete, account for all out-turn losses, and make sure that stock matches reality. Users can set up preventative and detective controls to make sure that this happens.