Using python, get real time crypto prices and conversion rates in excel

coinMarketCapRTD.py

from pyxll import xl_func, RTD
import threading
import time
import requests
from bs4 import BeautifulSoup

'''
  Date: 19-April-2021

  coin_rtd(crypto: str):
    Grab the price of the given crypto-currency from the coinmerketcap website.
  convRate_rtd(symb_from: str, symb_to: str):
    Grab the current conversion rate from the x-rates website.

  These scripts are used together with pyxll to be called from an Excel's cell.

  Installation:
    >> pip install requests
    >> pip install bs4
    >> pip install pyxll
    >> pyxll install

  How-to:
    1- Install the requirements
    2- Add this script into <Path2PyXLL>/examples/
    3- Edit the file <Path2PyXLL>/pyxll.cfg by adding this script in the modules section
    4- Open Excel
    5- For a coin value: in a cell type `=coin_rtd(<yourCoin>)`
        -> <yourCoin> can be a string or a cell which contains a string.
        -> Examples: `=coin_rtd("Ethereum")` / `=coin_rtd(B3)`
       For the conversion rate: in a cell type `=convRate_rtd(<from>, <to>)`
        -> <from> and <to> can be a string or a cell which contains a string.
        -> Examples: `=convRate_rtd("USD", "EUR")` / `=convRate_rtd(B3, B4)`

  Warning:
    These scripts play around with the soup code from the webpages, therefore:
      - <yourCoin> should match the full name of a coin, displayed on the website. You can check its validity by visiting the website
            https://coinmarketcap.com/
            and looking for <yourCoin> in the column "Name" (e.g. "SwissBorg")
      - <from> and <to> should match a real currency. You can check their validity by visiting the website
            https://www.x-rates.com/calculator/?from=<from>&to=<to>&amount=1
            e.g. https://www.x-rates.com/calculator/?from=EUR&to=USD&amount=1
      - if the websites changed, these scripts might now longer be working
      - if the cell is only displaying "Waiting...", it's most probably because your argument is incorrect
'''

class CoinMarketCapRTD(RTD):
    # URL to the website
    URI = 'https://coinmarketcap.com/fr/'

    def __init__(self, crypto):
        super().__init__(value="Waiting...")
        self.__symbol = crypto
        self.__thread = threading.Thread(target=self.__thread_func)
        self.__running = True
        self.__thread.start()

    async def connect(self):
        # Called when Excel connects to this RTD instance
        self.__running = True

    async def disconnect(self):
        # Called when Excel no longer needs the RTD instance
        self.__running = False

    def __thread_func(self):
        while self.__running:
            # Get the beautifulSoup of the webpage and convert it to a string
            HTML = requests.get(self.URI)
            soup = BeautifulSoup(HTML.text, 'html.parser')
            text = str(soup)
            # Look for the first pattern '"name":"<yourCoin>"' remove the part before
            text = text[text.find(f'"name":"{self.__symbol}"'):]
            # From now on, the first pattern '"name":"USD","price":' should be directly followed by the price of the coin, in USD
            text = text[text.find('"name":"USD","price":')+len('"name":"USD","price":'):]
            # The price is followed by the pattern ',"volume24h"'
            new_value = float(text[:text.find(',"volume24h"')])
            # Update the value in Excel
            if new_value != self.value:
                self.value = new_value
            time.sleep(1)


class ConvRateRTD(RTD):

    def __init__(self, symb_from, symb_to):
        super().__init__(value="Waiting...")
        symb_from = symb_from.upper()
        symb_to = symb_to.upper()
        self.__URI = f'https://www.x-rates.com/calculator/?from={symb_from}&to={symb_to}&amount=1'
        self.__symbol = symb_from.upper()
        self.__thread = threading.Thread(target=self.__thread_func)
        self.__running = True
        self.__thread.start()

    async def connect(self):
        # Called when Excel connects to this RTD instance
        self.__running = True

    async def disconnect(self):
        # Called when Excel no longer needs the RTD instance
        self.__running = False

    def __thread_func(self):
        while self.__running:
            # Get the beautifulSoup of the webpage and convert it to a string
            HTML = requests.get(self.__URI)
            soup = BeautifulSoup(HTML.text, 'html.parser')
            text = str(soup)
            # Look for the first pattern '1.00 {symbol} =' and remove the part before
            text = text[text.find(f'1.00 {self.__symbol} ='):]
            # From now on, the first pattern '"ccOutputRslt">' should be directly followed by the rate of the USD->EUR conversion
            text = text[text.find('"ccOutputRslt">')+len('"ccOutputRslt">'):]
            # The first part of the rate is followed by the pattern '<span'
            first = text[:text.find('<span')]
            # Then extract the second half of the rate
            second = text[text.find('"ccOutputTrail">')+len('"ccOutputTrail">'):]
            second = second[:second.find('</span')]
            # This is the new value
            new_value = float(first + second)
            # Update the value in Excel
            if new_value != self.value:
                self.value = new_value
            time.sleep(1)


@xl_func
def coin_rtd(crypto: str) -> RTD:
    return CoinMarketCapRTD(crypto)


@xl_func
def convRate_rtd(symb_from: str, symb_to: str) -> RTD:
    return ConvRateRTD(symb_from, symb_to)

Demo:
https://user-images.githubusercontent.com/53231772/115227062-45f27b80-a110-11eb-9f97-c46cc7ae5c66.mp4