NoCode and AI
Excel and VBA
Python and Data Science
Industry insights

Data Visualization: Creating a Cool Animated Chart

Alan Mourgues
February 20, 2024

Not long ago, I met up for coffee with a mate of mine with whom I'm working on some Python scripts. He asked, "Have you seen this James Eeagle guy on LinkedIn?" "No," I replied, "What about him?" "He creates amazing charts of financial data... you'd love it."

So, I naturally took a look and stumbled upon this beauty of an animation: interest rates predicted by the market over time. Have a look:

Immediately, I thought it would be cool to have a similar animation but for the oil price as predicted by the futures market (a chart called "term structure"). So, I asked him directly in his LinkedIn post (link to post):

I decided to take the challenge upon myself, as I thought it would be a good use case to demonstrate an example of how to go about creating a cool animation like this one for data visualization.

Do not get hung up on the specific data being shown here. The point is that there are better, more visually appealing ways to present information that may entice your audience and capture their attention. With the tools available today, anyone can do it.

There are various alternatives for creating an animated chart like the one above, but I’ll focus on two tools: Excel/VBA and Python.

With either, it obviously helps to have a clear idea in mind of what you want and how to work with the data sequentially. However, if you have little expertise in coding, or even if you have some but want to speed up the process, you can leverage ChatGPT, as I have consistently emphasized in previous editions.

Excel / VBA

The first step is to obtain the data and format it for use. Now, depending on the data, accessing and downloading it can be either easy or difficult. It might be publicly available for free, or it could be behind a paywall. In this case, I obtained the data from Bloomberg, a well-known financial database.

Below is a brief excerpt from the database, once in my Excel spreadsheet:

The table displays dates in the first column, followed by 12 prices, one for each futures contract maturity over the next 12 months. This indicates the current price of a barrel of oil for future delivery. The futures market enables buyers and sellers to hedge against volatility in the oil price. If this sounds too complex, simply think of the price of a futures contract as the market's prediction of what the oil price will be in the future.

Let's create a simple line chart from the first two columns, date and front month (consider this the spot price, for simplicity). Without any formatting, this is what we get:

Now, what we aim to do is add a new series to the chart for each futures curve, each consisting of 12 prices, one for each month. We need to determine which date corresponds to each of the 12 futures contract maturities, enabling us to plot them on the same Price vs Date chart.

Additionally, we want to add a futures curve to the chart only at the end of each month, rather than on every single date (daily granularity), to prevent the chart from becoming too cluttered and too heavy for the animation we plan to create. Here are the steps:

This is what the chart looks like at an intermediate step:

To create the animation, we aim to export each step as a separate image file, allowing us to combine them into a movie. Specifically, at the end of every month, we add the futures curve series to the chart and save that chart as an image.

Because doing this manually for many data points is impractical, we use a macro to automate the process over the entire range. The simplest approach is to record a macro while performing a few steps manually, and then edit the macro in the VBA editor.

If you're not very familiar with VBA, this is where ChatGPT can become your invaluable advisor!

Once you have all your images, there are many ways to stitch them together. For this example, I've used Python, with guidance from ChatGPT, as usual.

Prompt:

I got 150 .png image files and a .mp3 audio file. Write me a python script to turn the sequence of images into a movie, with the mp3 as background music.

We follow the instructions provided, and once the process is complete, this is the final animated video we obtain as a result:

Python

Let's be honest: coding enhances your capabilities—there's no doubt about it. If you know how to code, you have an edge.

However, I want to emphasize again that you can leverage ChatGPT not only to learn coding but also to accelerate your coding process, troubleshoot issues, and more. Dive in and start experimenting!

In this example, I intentionally approached the process from a no-coder's perspective, aiming to obtain as many step-by-step instructions as possible and to communicate in the simplest English possible.

I can’t share the ChatGPT instance as sharing chats with images is not supported yet, but here are the general principles of my querying process:

The chat screenshots below illustrate the principles mentioned above, giving you an idea of how the conversation progressed in simple spoken English:

.. And so the conversation continues until I converge on the desired solution.

The chart, axis, legends, text, logo, and background music were all created using Python.

However, for the fun of it, I added one extra step: I took the resulting .mp4 video to Microsoft Clipchamp, a free video editor in Windows 11, and added some floating text boxes to annotate three key historical events that underpin the price behavior in these periods.

This video-editing game can easily draw you in, as there’s always some extra touch that can be added, and it’s really fun. However, for the sake of this demo, this will do.

The animation includes:

Here is the end product (sound on):

What do you think?

Give me a shout if you'd like to have these files for your own experimentation, including the Excel workbook with the VBA code and/or the Python scripts mentioned above.

Before signing off, remember the core mission of CrowdField is to empower YOU to showcase your skills in the open market and monetize them effectively.

I'm on the lookout for individuals interested in conducting case studies. Whether your project is just a budding idea, partially implemented, or nearly complete, if you believe it has potential for monetization, I encourage you to get in touch and start a dialogue.

My goal is to assist you in refining your product to a market-ready state, launching it in our store, featuring it on our blog, and driving traffic to both your product and your personal brand. That's our value proposition. Now it's your turn to take that first step - get in touch!

More Tools

Explore a curated collection of valuable resources in our Store, both free and paid, all designed to help you upskill.

Alan is a Consulting Petroleum Reservoir Engineer with 20+ years of international industry experience. Alan is the founder of CrowdField, a marketplace that connects Oil & Gas and Energy businesses with a global network of niche talent for task-based freelance solutions. His mission is to help skilled individuals monetize their knowledge as the Energy transition unfolds, by bringing their expertise to the open market and creating digital products to sell in CrowdField's Digital Store.

Featured...

All blog posts

"O&G AI Wave"
Newsletter

Subscribe to O&G AI Wave – your indispensable guide to the evolving world of AI and NoCode and their practical applications for Oil & Gas professionals.

Our newsletter brings you the latest trends and insights in AI and NoCode technologies directly impacting the O&G sector.

Every issue is packed with expert analyses and practical tips to help you navigate and leverage these transformative technologies.

Don’t miss out on the opportunity to stay ahead of the trend and future-proof your career in this incredibly dynamic field.

How we can help

At CrowdField, our mission is to empower YOU—helping you showcase your skills in the open market and monetize them effectively. Here's how you benefit from being with us:

👉 Make your mark and find freelance opportunities by listing yourself in our freelancer directory to get noticed. Join us for free here.

👉 Turn your skills into digital products that sell. We'll help you polish, launch, and list them in our Store, promote them on our LinkedIn page, to our email subscribers, and feature your success in a case study on our Blog, amplifying both your product and personal brand. Whether you're at the idea stage, midway through implementation, or nearing completion, if you see potential for monetization, we're interested. Take the first step by getting in touch to start a conversation at hello@crowdfield.net.

👉 Discover bargains in our digital store with heavily discounted prices during our market discovery period. Take advantage of these limited-time offers as we expand our network. Dive in now and find your gem! Got to Store