Updated April 2020
Excel does support getting real time data into the worksheet with the RTD function, but this requires programming a COM server with which the RTD function communicates.
Power BI is able to make use of streaming data via API connections or PubNub, but what if I just want to pull some live data directly into a cell?
This article describes a way to get real time data into Excel using HTTP requests.
There is another article that describes how to get real time data into Excel using sockets.
With PyXLL I can do just that. PyXLL is an add-in that allows you to write Python code and use the results in Excel. Not only can you pull in real time data, you can write your own functions, amongst other things, making use of the many Python libraries available.
PyXLL uses a class derived from RTD which when combined with your own Python code, allows you to stream real time data.
Before You Start
You will of course need an installation of Python on your PC that will be used by PyXLL in Excel. Download and install the latest version from here : Get Python - it's free!
Once that's done you can get PyXLL from here : PyXLL, which is available for a 30 day free trial, after that there is a charge for continuing to use it.
Real Time Data
The world is awash with mentions of IoT (Internet of Things) streaming data from devices like sensors that tell you the temperature of the water in your fish tank, to what your cows are doing.
If you had a stream of data from an IoT device you could certainly use the following method to read and present the data in Excel. But in this post I'm going to write code for two examples, the price of crypto currencies, and some random numbers from a web based random number generator.
API's
To get such information it can be made available by what's known as an API - application programming interface. Which is a way of asking for and getting some data from a program, website or some other similar service.
For the crypto currencies, CoinCap provide a simple API which gives you information including the price for the currency you specify.
If I want to get the price of Bitcoin all I need to do is visit https://api.coincap.io/v2/assets/bitcoin
If you click that link, rather than see a nicely laid out website, you'll just see a bunch of text like this
{"data":{"id":"bitcoin","rank":"1","symbol":"BTC","name":"Bitcoin","supply":"18141812.0000000000000000","maxSupply":"21000000.0000000000000000","marketCapUsd":"134720405124.6647178192585068","volumeUsd24Hr":"3323508659.7492937215632559","priceUsd":"7425.9619229140241239","changePercent24Hr":"1.4005161160705637","vwap24Hr":"7370.9732287929146683"},"timestamp":1578185530218}
That's because the website is expecting to be sending the data back to a program so has formatted it into a JSON response the program can easily use. This is not meant for human eyes so isn't meant to look pretty. When we get this data we manipulate it in our Python code and return whatever we want to Excel, which in this case is the price.
If you want to you could make use of all the other information too but I just want the price for this example.
So by making a call to the CoinCap API for each currency I specify, it sends me the informaton related to that crypto currency.
It works the same way for the random number generator. My Python code makes a call to the random number generator at https://random.org, and the website sends me back a random integer.
Try it yourself https://www.random.org/integers/?num=1&min=1&max=99&col=1&base=10&format=plain&rnd=new
PyXLL RTD Class
By adding this RTD class into your code and modifying __thread_func to do the work of calling the API's and processing the result, we get our code to update our currency prices and random numbers in real time.
Crypto Currency Price Updates
Random Number Generator
Calling the Functions
By defining a function for both of these bits of code through PyXLL, Excel is able to access them like it would any other workbook function.
To get the price for Bitcoin
=coin_price_rtd("bitcoin")
Note that the currency name must be lower case.
You can pass in a cell reference rather than a string if you want to e.g.
=coin_price_rtd(LOWER(A1))
Where A1 contains the currency name.
For the random numbers, use this next function which doesn't require any arguments
=random_rtd()
Here are both functions in action.
I'm multiplying the random numbers by a decimal to make them more interesting, and I've added some conditional formatting to make the numbers visually appealing.
The top 50% of the currencies by price are green, the bottom 50% are red. In the workbook you can download just below, I've included just 3 currencies, but I'm sure you can add any others you want.
For the random numbers, any number greater than the average of all the numbers, is green.
Download the Excel Workbook and Python Code
In order to get this to work you'll need an installation of Python and to have installed the PyXLL add-in as described at the top of this article.
My workbook and sample Python code are provided for download as a ZIP file. Enter your email address below to get these.
Summary
If you know Python or want to try your hand at it, PyXLL is definitely worth a look. And if you want to get real time data into Excel it looks like a good solution.
Using Python it's also possible to get real time data using sockets, and you can bring real time data into Power BI with PubNUb.
Excel Mini Charts
Gabriel
Hi,
Have you tried using Excel-DNA in creating RTD servers?
It’s free and works with Visual Studio 2019 Community Edition, also free.
Philip Treacy
Hi Gabriel,
No I haven’t. I don’t do .NET programming but I guess I can look into it.
Regards
Phil
Rob landes
Please tell how to convert this live data into open-high-low-close for a 3 /5min interval
Philip Treacy
Hi Rob,
The first thing you (obviously) need is to get the OHLC data. You then need to record this in excel before plotting.
Have you written any code to do this?
Please start a topic on the forum and attach any/all workbooks and files you have and I’ll take a look.
Regards
Phil
HG
Is there an email I can contact you about getting your file to work properly?
HG
I also have python and pyxll installed. Thanks so much for your help!
Philip Treacy
Hi HG,
You can start a topic in the forum and attach your files to that. I’ll be able to have a look at the issue there.
Regards
Phil
HG
Hi Phil – I was able to get it running, thanks! However I noticed that after a while the currency prices will stop updating. Is there a way to prevent this?
Philip Treacy
Hi HG,
You’d need to check if the issue is with the API, Python or Excel. Is the Python code still running and receiving updates? If you enable logging you can see. It may be an API throttling issue.
Regards
Phil
Kevin Hankins
Why not just do directly in an Excel VBA function??
Philip Treacy
Hi Kevin,
How would you do this? If you use the VBA RTD function you have to create your own RTD server component
https://docs.microsoft.com/en-au/previous-versions/office/troubleshoot/office-developer/create-realtimedata-server-in-excel
PyXLL/Python abstracts all of that away from you so you just need to use a Python function call. I view that as a lot easier to implement.
Regards
Phil
Henrik Lindberg
Hi,
I am unable to use real-time-data-in-excel.xlsx file .This error occurs #NAME? I have downloaded pyxll and phyton it they are successfully running.
Philip Treacy
Hi Henrik,
#NAME would indicate that excel is looking for a unction it can’t find. Does the Python function name match the name you are calling in Excel?
Can you please start a topic on our forum and supply the workbook and Python file(s) for em to investigate further.
Regards
Phil
Ciprian
Hi Phil,
Tinkerer here 🙂
The endpoint “https://coincap.io/page/” is deprecated and no longer works.
I changed it to “api.coincap.io/v2/assets”, but Excel returns either #NoLatestPrice or #N/A and I can’t figure out what’s wrong.
I know that the new endpoint provides a different response and the code needs some changes.
Could you provide some clues or even better an adjusted .py file?
Thanks for sharing,
Cip
Philip Treacy
Hi Ciprian,
I’ve updated the Python code to work with the new CoinCap API. Download the sample zip file to get the new code.
Regards
Phil
Pranay A
Unable to use real-time-data-in-excel.xlsx file .Error of #NAME? is displayed .
How to integrate moth_random_rtd.py and moth_coin_rtd.py files?
P.S. I have downloaded pyxll and it is successfully running.
Philip Treacy
Hi Pranay,
Have you installed Python?
To help any further I’d need to see your files so please start a topic on the forum and supply these – both Excel workbooks and Python.
Regards
Phil
Pranay A
How to compare RTD to its last updated value which is a second ago. What is the formula for it. For example if current RTD value is 45 and RTD value a second ago is 44, what is the formula to determine if it has increased or decreased?
Philip Treacy
Excel can’t keep track of a value as it changes, it doesn’t know about a past value and its new current value. So I’d modify the Python function that returns the RTD so that it returns both the new value and some indication how that value has changed.
The code
if self.value != new_value:
self.value = new_value
could be changed to something like
if self.value != new_value:
self.value = self.value + ‘:’ + new_value
so you are retrurning the current value and the new value. You can then do something with these in excel to indicate that the value has increased or decreased.
Regards
Phil
Prashant N
Can we save all this RTD data from opening to close the market with 1 second interval in cvs format ?
If yes then how and what we need for this ?
Philip Treacy
Hi Prashant,
Yes you could do this. As the RTD is coming in via the Python functions, I’d modify the Python function to save the RTD into a CSV.
https://realpython.com/python-csv/
Regards
Phil
Prashant N
Hi Philip
Thanks for your prompt and kind reply.
One thing more, how to import this RTD from excel to python.
Philip Treacy
Hi Prashant,
The data is already coming in via Python, then to Excel. I’m a bit confused by your question.
Prashant N
Hi Philip
Data is coming in excel first then have to import it in Python and then have to write that complete data in cvs.
Philip Treacy
Hi Prashant,
No the data is coming in from the Python function, then being sent to Excel. That’s why I said you should alter the Python function to write the data out to a CSV file.
You will need to add something like this to the moth_rtd.py file
import csv
from datetime import datetime
# dd/mm/YY H:M:S
dt = datetime.now().strftime(“%d/%m/%Y %H:%M:%S”)
with open(‘rtd.csv’, mode=’a’, newline=”) as rtd_prices:
rtd_writer = csv.writer(rtd_prices, delimiter=’,’)
rtd_writer.writerow([dt,new_value])
Place the import statements at the top of the file.
The remaining code to write the CSV can go into def __thread_func(self): whch is where new_value is the real time price updates.
Regards
Phil
JeVs
Hi,
I have following formula in RTD that I’m trying to make it dynamic using reference cells.
Static Formula
RTD(“nest.scriprtd”,,”nse_cm|APPL-EQ”,”LTP”)
Dynamic Formula
RTD(B2,,D2,E2)
Where
B2 = nest_scriprtd
D2 = nse_cm | APPL-EQ
E2 = LTP
For D2, I am unable to get rid of ‘|’ to make formula dynamic. Please see if you could figure it out.
Philip Treacy
Hi JeVs,
I don’t understand ho removing | makes the formula dynamic. I don’t understand why you can’t remove the | can’t you just edit the value in D2?
All of this would be easier to figure out with a workbook and data.
Regards
Phil
Hardik
Hi this is very nice solution after old Google finance gone…
Hope this works I will learn Python and post you in 2-3 months … Please share file on it will be of great help..
Philip Treacy
Thanks Hardik.
You can download the workbook and Python files from the blog post above.
Phil