• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
  • Login

Real Time Data in Excel

You are here: Home / Excel / Real Time Data in Excel
real time data in excel
May 24, 2018 by Philip Treacy

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

real time python code for currency updates

Random Number Generator

real time python code for 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.

Crypto Currency Prices in real time

Random numbers in real time with Python

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.

By submitting your email address you agree that we can email you our Excel newsletter. You can unsubscribe at any time.
Please enter a valid email address.
Excel Workbook & Python Code Excel-Real-Time-Data.zip

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.

real time data in excel
Philip Treacy

Microsoft Power BI Community Super User Logo

AUTHOR Philip Treacy Co-Founder / Owner at My Online Training Hub

Systems Engineer with 30+ years working for companies like Credit Suisse and E.D.S. in roles as varied as Network & Server Support, Team Leader and Consultant Project Manager.

These days Philip does a lot of programming in VBA for Excel, as well as PHP, JavaScript and HTML/CSS for web development.

He's particularly keen on Power Query where he writes a lot of M code.

When not writing blog posts or programming for My Online Training Hub, Philip can be found answering questions on the Microsoft Power BI Community forums where he is a Super User.

More Python Posts

charting real time data in excel

Charting Real Time Data in Excel

Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
using jitter to avoid over plotting

Using Jitter to Avoid Over Plotting in Power BI

Plotting data that has one variable where values are similar, can result in points that are plotted over each other. Use jitter to avoid this overplotting.
Real time data in Excel using sockets

Real Time Data in Excel Using Sockets

Get real time data into Excel using Python web sockets. Once established, sockets stay open and require little overhead to send messages.
writing udfs in excel with python

Writing UDFs in Excel With Python

Write Excel UDFs in Python. Use the power of the vast Python libraries for data science, statistics, web scraping or database connectivity.
Plot an equation in Excel using Python

Plot an Equation in Excel Using Python

How to plot an equation in Excel using Python. Easily plot your functions or data with just a few lines of code. Sample workbook and code available.

More Excel Posts

8 Excel in-built AI Tools

Built-in Excel AI Tools

Clean, analyze and visualize data with these Official Excel AI tools by Microsoft. Including formula writing, PivotTables, Charts and more.
excel date and time

Excel Date and Time

Everything you need to know about Excel date and time. Includes comprehensive workbook with every date and time function, plus PDF version.
10 mistakes to avoid when using excel formatting

10 Mistakes to Avoid With Excel Formatting

These are the Excel formatting habits that drive me crazy and what you should do instead to ensure you use formatting effectively.
linear regression

Excel Linear Regression

Excel linear regression is easy with the built-in tools. Use charts to plot linear regression or use the Data Analysis Toolpak.
speed up slow excel files

How to Improve Excel Performance

How to improve Excel performance and the various causes of slow Excel files so you can speed up Excel and avoid problems in future.
Securely Share Excel Files

Securely Share Excel Files

Securely share Excel files stored locally, on OneDrive or SharePoint. Prevent editing or downloading, specify who can open and edit the file.
excel check boxes

Interactive Excel Check Boxes

Excel check boxes are interactive elements you can link to formulas, charts, conditional formatting and more.
tips for working in multiple excel files

Hacks for Working in Multiple Excel Files

Awesome tips for navigating, arranging and working in multiple Excel files. Guaranteed to streamline your workflow and increase productivity.
chatgpt for excel

ChatGPT for Excel

Using ChatGPT for Excel can be hit and miss. Learn the best uses for ChatGPT to make your Excel life easier and what to avoid using it for.
excel templates

Where to Find Free Excel Templates

Where to find free Excel templates and how to create your own Excel templates. Using templates saves time and effort.


Category: ExcelTag: Python
Previous Post:Excel Mini Charts
Next Post:Real Time Data in Excel Using SocketsReal time data in Excel using sockets

Reader Interactions

Comments

  1. Gabriel

    September 11, 2020 at 3:28 pm

    Hi,

    Have you tried using Excel-DNA in creating RTD servers?
    It’s free and works with Visual Studio 2019 Community Edition, also free.

    Reply
    • Philip Treacy

      September 17, 2020 at 7:58 am

      Hi Gabriel,

      No I haven’t. I don’t do .NET programming but I guess I can look into it.

      Regards

      Phil

      Reply
  2. Rob landes

    August 15, 2020 at 6:24 pm

    Please tell how to convert this live data into open-high-low-close for a 3 /5min interval

    Reply
    • Philip Treacy

      August 17, 2020 at 9:48 am

      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

      Reply
  3. HG

    June 17, 2020 at 2:50 pm

    Is there an email I can contact you about getting your file to work properly?

    Reply
    • HG

      June 17, 2020 at 2:54 pm

      I also have python and pyxll installed. Thanks so much for your help!

      Reply
      • Philip Treacy

        June 17, 2020 at 3:07 pm

        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

        Reply
        • HG

          June 18, 2020 at 6:05 am

          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?

          Reply
          • Philip Treacy

            June 18, 2020 at 7:49 am

            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

  4. Kevin Hankins

    May 9, 2020 at 12:53 am

    Why not just do directly in an Excel VBA function??

    Reply
    • Philip Treacy

      May 9, 2020 at 9:29 am

      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

      Reply
  5. Henrik Lindberg

    April 27, 2020 at 7:32 am

    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.

    Reply
    • Philip Treacy

      April 27, 2020 at 9:40 am

      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

      Reply
  6. Ciprian

    March 29, 2020 at 11:07 pm

    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

    Reply
    • Philip Treacy

      April 3, 2020 at 6:51 pm

      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

      Reply
  7. Pranay A

    January 10, 2020 at 2:02 am

    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.

    Reply
    • Philip Treacy

      January 10, 2020 at 9:56 am

      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

      Reply
  8. Pranay A

    January 4, 2020 at 4:16 pm

    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?

    Reply
    • Philip Treacy

      January 5, 2020 at 10:47 am

      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

      Reply
  9. Prashant N

    August 30, 2019 at 8:32 am

    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 ?

    Reply
    • Philip Treacy

      August 30, 2019 at 1:31 pm

      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

      Reply
      • Prashant N

        September 8, 2019 at 3:43 am

        Hi Philip
        Thanks for your prompt and kind reply.
        One thing more, how to import this RTD from excel to python.

        Reply
        • Philip Treacy

          September 8, 2019 at 11:55 am

          Hi Prashant,

          The data is already coming in via Python, then to Excel. I’m a bit confused by your question.

          Reply
          • Prashant N

            September 9, 2019 at 3:49 am

            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

            September 9, 2019 at 11:10 am

            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

  10. JeVs

    February 22, 2019 at 8:33 pm

    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.

    Reply
    • Philip Treacy

      February 23, 2019 at 3:52 pm

      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

      Reply
  11. Hardik

    May 27, 2018 at 4:07 pm

    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..

    Reply
    • Philip Treacy

      May 28, 2018 at 9:23 am

      Thanks Hardik.

      You can download the workbook and Python files from the blog post above.

      Phil

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Popular Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel Office Scripts
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate
  • Sponsor Our Newsletter

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.