• 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

Real Time Data in Excel Using Sockets

You are here: Home / Excel / Real Time Data in Excel Using Sockets
Real time data in Excel using sockets
May 31, 2018 by Philip Treacy

Last week I wrote about getting real time data into Excel using PyXLL and Python code.

I described a way to do this using HTTP connections. HTTP is the type of connection your browser uses to read web pages.

In this article I'm looking at using sockets to get real time data into Excel.

Sockets

A socket is connection made between a client device (e.g. your PC) and a server (e.g. a website, a server, a sensor, etc) that allows the client and server to send each other messages.

This socket connection, once established, stays open and requires little overhead to send messages whilst open.

Think of it like this. You call your best friend on the phone to have a chat, but when you are finished talking, you keep the call connected in case you think of something else you want to talk about in a minute. By not hanging up, you don't have to dial the number again and start another conversation when you think of something new to discuss.

Compare this with a HTTP connection. If your PC (the client) wants to read a web page from our blog you have to click a link. By clicking this link you are telling your browser to request the contents of the blog post from our website (the server) and display it on your screen.

Before any part of the blog post can be sent to you, the browser has to send a request to the server asking it for the blog post. The server then responds informing your browser if it is able to send the blog, before it actually starts to send any part of the page.

Every time you click a link to load another blog post (or any web page) your browser and our website have to go through this whole routine again.

Compared to sockets, the HTTP connection has more overhead in its communications. Every time a page is requested, the browser and server have to talk to each other to set up the connection before any data can be sent.

Once a socket is established it remains open and the server can just send a message down it to the client. The client doesn't have to keep asking the server for the next piece of information.

For real time systems this is beneficial as the client can just sit there and wait for the next piece of data to arrive, like a change in a stock price, or the latest reading from a sensor.

Implementing Sockets in Excel With PyXLL and Python

I'm again going to use the PyXll Python add-in. You'll need to install both PyXLL and Python if you want to give this a go.

As explained in the last post, my Python code will use a modification of Excel's RTD class to pass data into the worksheet. Python works away in the background and notifies Excel when new data has been received which is then displayed on the sheet.

For this exercise I'm going to use the websockets API provided by GDAX to monitor, amongst other things, the price of Bitcoin in USD.

To set up the socket I'll need to install the websocket-client package in Python.

The Code

Once you've imported the socket library into Python you create the socket like this

Create a socket

To get updates from GDAX on the Bitcoin-USD price, subscribe to the ticker channel, specifying the currency pair BTC-USD.

You can subscribe to multiple currency pairs here in the same request if you wish.

Subscribe to socket

All the heavy lifting is done by the GDAXRTD class

Real time data class

and it's in __thread_func that we listen for updates from GDAX and then process the messages we receive.

Listening to socket for message

Any message we receive is in the form of a JSON object and is stored in a variable called result.

The JSON data looks like this

{"type":"ticker","sequence":5981458471,"product_id":"BTC-USD", "price":"7361.05000000", "open_24h":"7493.86000000", "volume_24h":"5764.09338556", "low_24h":"7272.71000000", "high_24h":"7538.14000000", "volume_30d":"273076.23540644", "best_bid":"7361.04", "best_ask":"7361.05", "side":"buy","time":"2018-05-31T03:20:03.484000Z", "trade_id":44233953, "last_size":"0.13707180"}

Processing the Data Updates

PyXLL can only return a single piece of data to Excel. If we are trying to get the current price, bid and ask prices, how to do this?

By converting the data object sent to us by GDAX into a string, we can return that string to Excel and then use string functions like MID and FIND to extract the values we want.

Once we've extracted these numerical values, we can use the VALUE function to convert the number stored as a string, into a number.

In my sample workbook all of these formulae are included for you to examine.

Here's what the sheet looks like with the real time data updates coming in.

Real Time Data In Excel from Web Socket

Charting This Data

As we are only able to send one value to Excel at any one time, the numbers we are getting are points in time which we can't produce a meaningful chart from. In order to create useful visualizations of the data, we need to store the data as it changes over time.

We could do this by writing some VBA to add every updated value to a dynamic named range, and chart that.

We could write the data to a file and chart that but we'd lose the ability to see the data in real time.

There are other solutions we could use involving Python too, but for now I'll just leave it up to you to figure this out and maybe I'll write another post dealing with this.

Visual Studio Code

I had to reinstall Anaconda for this post and in doing so also installed Microsoft's Visual Studio Code source code editor and so far it has been great. All the images of code above were taken from it.

As you can see from the screen shots above, it obviously has color coding, and also provides debugging and intellisense amongst other things. If you are looking for a code editor, give it a go, it's free.

Download the Workbook and Python Code

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 (Zip Archive)
Real-Time-Data-In-Excel-Using-Sockets.zip
Real time data in Excel using sockets

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

Real Time Data in Excel

Get real time data like stock or currency prices into your Excel workbook using a little bit of Python code. Sample workbook and code available.
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

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.
Easily Remove Password Protection from Excel Files

Easily Remove Excel Password Protection

How to remove Excel password protection when you’ve forgotten the password. Works for sheets, workbooks and read only files.
Import data from a picture to Excel

Import Data from a Picture to Excel

Import data from a picture to Excel. Works with pictures from a file or the clipboard and loads it to the spreadsheet.
excel online

5 Excel Online Features Better than Desktop

5 Excel Online Features Better than Desktop including searchable data validation, track changes, single line ribbon and more.


Category: ExcelTag: Python
Previous Post:real time data in excelReal Time Data in Excel
Next Post:Office 365 vs Perpetual Licences

Reader Interactions

Comments

  1. G Malik

    September 3, 2020 at 6:38 pm

    thanks

    could you please tell us how can we use binance websockets to get all coins data

    thanks again
    viki

    Reply
    • Philip Treacy

      September 5, 2020 at 4:04 pm

      Hi,

      Have you looked at the Binance WebSocket API documentation on how to do this? I haven’t checked it out but you’ll need to modify the code to connect to the websocket endpoints then process what they return.

      If you’ve given this a go and are stuck please post your code on the forum and I’ll take a look.

      Regards

      Phil

      Reply
  2. sam

    February 1, 2019 at 12:52 am

    This is really cool,

    I Would love to see an example for Bitmex. (They have a comprehensive API)

    I’m currently using REST methods through Excel VBA but would love to learn the Web Socket method especially for “Getting” data.

    Just cant seem to get off the mark using the examples above.

    Reply
    • Philip Treacy

      February 6, 2019 at 10:14 am

      Hi Sam,

      I’ll have a look at BitMex and see if I can get anything working.

      Regards

      Phil

      Reply
  3. Dan McGovern

    December 21, 2018 at 3:49 am

    so the guys from pyxll were gracious and gave me a key for their installation. for this project i would create a sheet for all of coinbases coins and share that on your site if thats ok. so my question is, somehow when i run the moth_ws.py program, it says there is “no module named pyxll”, is there another file that i need to include (also websocket says it needs help, but i did do the install according to your article. with just a little bit of help from you i can get this going>

    Reply
    • Philip Treacy

      December 21, 2018 at 9:56 am

      Hi Dan,

      Your moth_ws.py file should be in the same folder as PyXLL e.g. c:\pyxll

      If that doesn’t fix it, without seeing your files I cant say what’s happening. Can you please start a post on the forum and attach your Excel workbook, Python code and the PyXLL config file.

      Cheers

      Phil

      Reply
  4. Dan McGovern

    December 20, 2018 at 10:47 pm

    having problems installing python and pyxll. when i go to addins in excel, it tells me its the wrong version and keeps looking at python27.dll. when i search for it, i find it in downloads from a gimp install. how to fix, i really want to do what you are doing in this article, i am a coinbase member. so any help is appreciated.

    Reply
    • Philip Treacy

      December 21, 2018 at 10:01 am

      Hi Dan,

      Have you got both Python 2 and 3 installed? Best off just to use 3 if you can.

      Phil

      Reply
  5. Vincent Antonio

    June 8, 2018 at 11:29 am

    thanks for the very useful tips!

    Reply
    • Philip Treacy

      June 8, 2018 at 1:01 pm

      You’re welcome.

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

launch excel macros course excel vba course

Featured 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

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

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

Company

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

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.