Understanding Bloomberg data usage (part 1)

Just like many others I am currently also working from home too due to measures put in place to fight the SARS-CoV-2 virus, better known as the Coronavirus.

Not having to commute to work and having a stop put on most social activities means I have time to work on things I kept procrastinating on. One such thing is to write about my past projects on LinkedIn. The purpose of this is to get feedback for improvement, provide value to whoever is reading, and a bit of self-promotion if I can be brutally honest.

One such side project was a set of tools to help data and vendor managers to better analyze their Bloomberg data service invoice. I haven't kept up to date with the latest developments so what I am about to tell might not apply anymore. But I hope the basic principles at least might be of use to someone.

Let me start with a disclaimer: I am not here to endorse or promote any product, service, or solution, only to help others better understand them. Each clients' situation is unique and requires a unique approach. Your mileage may vary. Please don't sue me!

In the past I was involved in various Bloomberg Data License cost savings projects for multiple clients. After some time a common pattern started to emerge.

1. The business wants to reduce/optimize their costs;
2. Usually, they want to start with data as it is not tangible, but yet very expensive;
3. No clear insights into usage or where to cut costs;
4. Project is abandoned or wrong decisions are taken which have an even bigger impact.
5. Rinse and repeat the cycle.

Asking a better question

Every analysis ideally involves: clearly stating the goal, identifying the root problems, and using the information that is already available to work towards a solution. It all starts with asking a better question. Whilst the goal would be to save money on data costs, the first question should be: Can we save money on our data usage and where/how/how much?

To answer this question we need to analyze the readily available information starting with the invoices, the verification files, and contract details. But how do we interpret this information? The approach I took was: 

1. Making the invoice easier to understand;
2. Deep diving in the verification file to exactly understand the usage;
3. Link all this back to the business requirements and contract details.

In this post I will cover only the first point.

Formatting the Bloomberg Data License invoice 

There are two formats for the invoice. Either a PDF or a CSV file. The PDF format is great for payment instructions and archiving but no one wants to scroll back and forth through a document to make some sense of it.

The CSV format is better suited for analysis purposes, however, the file needs to be cleaned first. Some of the discrepancies include: formatting of dates and numbers, displaced headers and line breaks containing invoice incremental details.

Cleaning and formatting the data can be done with a simple Excel macro. This saves time and reduces the possibility of making errors. Once cleaned one can create various pivot-tables and start analyzing. Using Power BI is also a possibility.

D.I.Y. steps

Great! But what steps should you take to do it yourself? Well..that's tricky. Not all invoices are the same as each client tends to have a slightly different contract or product mix, thus yielding a slightly different invoice layout. Also, I am not 100% sure if the invoice format has stayed the same in the past couple of months. Assuming that was the case the following actions need to be applied:

• Perform a copy-paste values to remove all hidden formulas;
• Remove additional zero's in the quantity column;
• Trim all content to remove the leading and trailing spaces. This applies especially to the price and (sub)total amount columns;
• Check your decimal symbols. Depending on your computers language setting you might need to replace periods with commas or the other way around;
• Remove the descriptive rows. These are rows containing the invoice or account number or some other descriptive information.

Follow the above steps, or better yet automate them in an Excel macro or MS Access, process the data over a period of time, and start analyzing. Obviously, I cannot post an actual invoice or give too much graphical details in this post. But please feel free to contact me so I can help out.

A simple and elegant solution

The above approach works great. But I took this a step further. Excel is a great tool, but having a dedicated and easy-to-use tool would be much better. This tool is also easier to embed in a team compared to unsecured Excel files. I believe that the time of a data or vendor manager is better spend analyzing the results of the processing instead of formatting files, sharing macro's, double-checking if everything matches. etc.

How it works is simple. One simply uploads a CSV invoice to the tool. The tool cleans, formats, and analyses the data. When done it presents it in an easy to read format.

The user is now able to see a breakdown in asset classes or categories. Each line is also split in:

• Initial: the monthly one-off costs related to a single security-product request;
• Access: additional costs related to requests made above the daily allowance (usually 1);
• Metered: costs related to specific historical requests. This usually applies to pricing.

Furthermore, we are also able to see a historical overview of each single asset class, category, or a combination of both to recognize patterns.

It's a very simple tool. One which saves 10-20 min of manuals tasks and checks allowing the user to focus on that which matters most: the analysis. With enough data users can see patterns over time, do effective forecasting, and start analyzing how to reduce Access costs.

Faster, Better & Cheaper

The fact that we were able to process the invoice data over a period of time and extract patterns and cost-saving opportunities allowed me to provide very specific actions to the project plan and facilitate internal discussions around data use and ownership.

The invoice itself yields quite enough information to start an analysis. Whilst deeper data is found in the verification file, a simple change in point of view for the invoice can help users a lot.

I will admit that the above is not particularly rocket science. However, many business analysts will agree with me (I hope) that sometimes being able to craft your own tools tends to get the job done quicker instead of maintaining the status quo.

Stay tuned for the next post where I detail the benefits of analyzing the verification files and how a small 10 min change translates into yearly savings of over $100.000.

***Update***

View the next post in this series:

• 4 uses for the Bloomberg Data License verification reports