How an API Powers Up Your ETRM/CTRM (and Your Spreadsheets) + Use Cases

Key Takeaways

• Many risk managers use spreadsheets in their daily workflow to manage their trades, run reports, generate confirms and invoices, and more.
• Despite the benefits of spreadsheets, they also have limitations that pose a major risk when it comes to data accuracy, reliability, and security.
• Automating manual processes with help from an API interface for your ETRM can save risk managers time, increase productivity, improve accuracy, and unlock better business insights — all while still using their favorite reporting tools.

You likely have a lot of different sources for your trades and marking prices… and you probably have a lot of spreadsheets, too. But if you’re still managing your trades on spreadsheets alone, you’re risking accuracy — creating the potential for costly, time-consuming errors that could even tarnish your reputation.

Spreadsheet errors happen all the time. In fact, studies have shown that nearly 90% of spreadsheets contain errors. Despite this, many companies still rely on a frankensystem of cobbled spreadsheets to manage their mid-office processes (and, let’s be honest, their front and back-office too).

Without a solid system in place, this is downright dangerous. Having all your data in the wrong place (or too many places) can not only leave your reports vulnerable to inaccuracies but also unauthorized access — or worse, a complete security breach.

How many hours have you spent fixing an error on one of your spreadsheets and/or their downstream reports? How many times have you fixed a copy/paste error, or unknowingly used an outdated version of a spreadsheet?

If you’re like most risk managers, you need accuracy, security, and efficiency... but you just can’t get away from your spreadsheets. Fortunately, you can have your cake and eat it, too.

Table of Contents

1. What Is an API (Application Programming Interface)?
2. What Is a REST API?
3. An API in Risk Management Reporting
4. What a Good API Looks Like in a Modern E/CTRM

A modern ETRM gives you a single system of record, bringing all your trades across multiple exchanges and counterparties into one place. Better yet, it’s more accurate and complete, making it easy for you to see the correct position, mark it, and have the right calculations when and where you need them. On top of that, having an ETRM with a flexible API, used in tandem with your spreadsheets, will significantly reduce errors, keep your data safer, save time on manual processes, and even drive better business intelligence.

So, how will an ETRM with flexible APIs make your job easier and give you the information you need to make quicker and better decisions — while still keeping your spreadsheets? Let’s break it down.

What Is an API (Application Programming Interface)?

If you’re unfamiliar with APIs, you’ve probably used them more than you realize: they’re integral to applications across nearly every industry. Did you check the weather on your phone this morning? Did you use Apple Pay when you ordered something online? Did you like a post on LinkedIn? If so, you’ve used an API today.

Simply put: an API (Application Programming Interface) is simply a request-response messaging system. It is standard, reliable, and widely understood by programmers since they are compatible with various programming languages, including Python, Java, and Ruby.

While there are many types of APIs for various purposes, we’ll be focusing on RESTful APIs for risk management within an ETRM/CTRM platform.

What Is a REST API?

A RESTful API (or REST API) is a type of API that follows the REST (representational state transfer) standard. Plus, RESTful APIs are widely understood by developers, meaning your IT group likely has a programmer who can write scripts to access it.

With a RESTful API, applications communicate with each other through a cloud-based server, with rules that must be followed exactly, like a contract. When it comes to risk management reporting, a RESTful API is powerful because you can integrate your data across key systems, ensuring system interoperability and minimizing inefficiency.

An API in Risk Management Reporting

Since effective risk management hinges on accurately assessing exposures using the most up-to-date information, an API is a powerful tool — enabling trading organizations to automate report generation and support a variety of reporting requirements.

As a risk manager, here’s what you can do with an API connected to a spreadsheet:

• Get complete and accurate data… the first time
  Ever overlooked a position (or an entire row or column) in your calculations? Messed up a copy/paste? Uploaded or downloaded the wrong file? It happens to the best of us, and it’s nails-on-a-chalkboard awful… especially if it affects your downstream reports too.
  
  Because APIs transfer data the same way every time and enable automated calculations, you can be confident in the accuracy of position data and other critical information from the start. And if there are gaps in data needed for proprietary models, there’s no need to use estimates — they can be filled using near-real-time information pulled automatically from a market data source.
  
  
• Save time… BIG TIME
  When you get complete and accurate data the first time, you’ll save time by not having to re-run a report (and all the downstream data along with it) to fix a single mistake.
  
  An API data connection on your spreadsheet can be configured to automatically refresh data and calculations, so you can generate reports using near-real-time information. This eliminates the need to manually download and upload data from external sources, rebuild pivot tables, recreate summary reports, etc. And, yes, this also means your downstream data gets refreshed too — the same way, in near-real time — all with a click of a button.
  
  
• Unleash your reporting superpowers with fast + actionable insights
  Imagine completing all your settlement reconciliations just by refreshing your API in the morning. Or creating custom pivot tables in Excel to combine your ETRM’s valuations with your offline valuations before you’ve brewed your coffee. Or running a custom trading limit compliance check report whenever you want — instead of whenever you’ve blocked out time in your schedule.
  
  APIs provide access to powerful reporting capabilities by feeding data into advanced reporting and data analysis tools such as PowerBI, Tableau, and custom-built analytics platforms. Once connected via the API, your data will upload automatically, in near real-time, as it loads into your E/CTRM.

What a Good API Looks Like in a Modern E/CTRM

When you’re comparing ETRM/CTRM systems, make sure to think about how a particular API can best fit your needs. Here’s what a good API should have:

The ability to extract a wide range of E/CTRM data

The API should be flexible enough to extract a comprehensive range of raw data from the E/CTRM system. This ensures that you can meet complex reporting needs and easily integrate data into financial reporting and invoicing applications, as well as other key systems.

Data that would be useful for you to extract includes:

✔  Counterparty data
✔  Trade data
✔  Valuation calculations
✔  Inventory data
✔  Fees
✔  Products
✔  Forecasts
✔  Settlements
✔  Market data/curve builds

Automated data loading

The API should enable your IT team to write custom scripts that quickly load essential portfolio datasets (e.g., trades and forecast data) into your system by date, product, or asset. Valuations and reports will then update automatically, in near-real time, as new price data enters the system — this saves time, mitigates errors, and enables users to get their data out of the system without help from IT.

Comprehensive documentation

Thorough API documentation accelerates time to value and ensures easy adoption — even for users with limited technical experience. Look for documentation that contains detailed endpoint specifications and code test samples.

Stringent security standards

Make sure your sensitive data is fully protected. Ensure vendors enforce stringent security standards and keep your data secure using:

• API keys and tokens for user authorization
• Traffic encryption to prevent data interception during transit
• Extensive security testing and protocols for data protection and monitoring

→ Bonus points if vendors are SOC 2-certified. This voluntary certification demonstrates a commitment to maintaining high security standards.

Reporting That’s Smarter and Faster With APIs

Solely relying on manual processes for risk management reporting is risky. Thankfully, a modern ETRM system with APIs can help you keep your spreadsheets, do your job faster and better, and get the accurate reporting you need — allowing you to save time, feel confident in your reporting, and unlock meaningful business intelligence while you’re at it.

Read our blog post on multi-tenant SaaS ETRM/CTRMs to learn more about the benefits of using a modern ETRM/CTRM system.