In this video, we are going to show you how to create a live crude oil price dashboard in PowerPoint using our DataPoint add-on for PowerPoint. Click on the video below to see our process.
Live Crude Oil Price from Web URL
Our first attempt was to gather the information from Yahoo Finance. Yahoo Finance has extensive stock pricing information. We found the price information for West Texas Intermediate Crude (WTI) which has a symbol of CL=F.
Now to get this chart into PowerPoint. I created a PowerPoint slide and then did the following:
- Inserted a random picture from my hard drive
- Clicked on the picture, then chose [DataPoint] from my PowerPoint menu
- Then chose [Web url]
- Then paste the url into the field
- Then choose how often I want the picture to update
This leaves me with a PowerPoint slide with a continually-updating price from Yahoo Finance.
Price API
While Yahoo Finance gives us some information and worked for a single price, there is a lot of extra information on the page such as Yahoo’s branding, advertising, news, etc. And we could only put one price on a page. We want to have more control. For this we need a crude oil price API – something that can give us the numbers we need without all the extra advertising, branding, and content.
For our crude oil price dashboard, we found a great provider, Finnhub.io. Finnhub has an API for stocks, foreign exchange, cryptocurrencies and other data.
We went to Finnhub and created a free account to get our API.
Once we have signed up for free and have our API from Finnhub, we looked through the documentation to find the url/api link for the crude oil prices we wanted, WTI and Brent Crude Oil. We found Finnhub support was very responsive and helpful in helping us with our questions.
We went to the [DataPoint] menu in PowerPoint and clicked on the [List] command to create a connection. Then we clicked on the [JSON] option which is used for this and many other types of APIs.
Next, we create a query by clicking on the [Add Query] option. Then we click on JSON and can see the data show up in the preview. It is important to rename your queries so you know where you are drawing the data from.
Create Crude Oil Price Table
Now that we have these live data connections, let’s show them in a table. Click on the [Insert] then [Table] command from the standard PowerPoint menu to insert a table. Don’t worry about how many rows and columns for now as we will let the data arrange that automatically.
Once you have your blank table created, click on the [DataPoint] menu and then click on [Table]. You can tick the boxes to automatically adjust the number of rows and columns – this lets the data automatically create additional rows and columns as needed.
This gives you a table that looks like this.
- c=closing price
- h=high price
- l=low price
- o=opening price
- pc=previous closing price
But this table has some extra information that isn’t needed – ID and T for timestamp. To remove these on the second table, I used the connection properties to choose the [special column selection] option and changed it to columns 2-6, leaving off column 1 with the id and column 7 with the timestamp.
Live Crude Oil Price using Text Boxes
While these first two methods work, the way to get the maximum control over the pricing placement and labels is to work with text boxes.
So on a new slide, I added a text box with the names of the prices I want to show, then created a second text box to hold each price using the standard PowerPoint [insert] then [textbox] command. Then I go to the [DataPoint] menu, click on the textbox I want to work with and choose the [textbox] command from DataPoint and choose the data I want in each text box.
We hope you have enjoyed these three methods for showing live crude oil prices in PowerPoint. If you have questions about how to use our software for a wide range of real-time dashboards, digital signage, or reporting, contact us.
