Part IV – with R in Excel – call Python | R-Bloggers

Introduction

In the last message in this series I will look at the calling of Python by R. Although Excel now offers a way to call Python scripts directly, =PY() Formula in a worksheet, there are still occasions that it is useful to call Python via R. It appears, for example, that importing yfinance Produces a ‘module that is not found’ with the help of Excel’s function. According to the documentation, yfinance Is not one of the open source libraries that supports the Excel Python Secure Distribution. To bypass this problem, we can use the R package Reticulate. This allows us to do Python scripts from R. Laden and implement, as we have seen in the previous parts of this series, the Excelraddin This allows us to perform R -Scripts from an Excel worksheet. And compiling these two is fairly simple.

The workbook for this part of the series is: “Part IV – R in Excel – Python.xlsx calling”. As earlier, the worksheet contains ‘references’ links to external references. The worksheet ‘Libraries’ loads extra (non-default) packages. In this demonstration I use the reticulate Package and this is loaded here. The ‘Datasets’ worksheet contains the data that is referred to in the worksheets. In addition, there are two auxilliary workksheets: session -info and setup. The session information picks up information about the R environment and R version. The Setup worksheet carries out what initialization is required for the reticulate package. First we point to the Python -executable file that we want to use. We then confirm that the Python version is actually the one who is being asked. Finally, we have set up a section where we can return when there are errors of reticular and python itself. It is worth ensuring that these cells are evaluated correctly.

Ticker info

This worksheet requires a number of fundamental ticker info for a number of shares. The first step is to load the script Ticker_Data.Py And make sure it compiles.

The Python Script

The script consists of two main functions: get_ticker_data And get_ticker_weights.

"""
ticker_data.py

Retrieve ticker data from yfinance

"""

import pandas as pd
import yfinance as yf


def get_value(info: dict, key: str):
    """
    Retrieve a value from a ticker info dictionary
    or None if the key doesn't exist
    """

    if info is not None:
        if key in info.keys():
            return info[key]

    return None


def get_items(symbol: str, fields: list) -> list:
    """
    Retrieve data from the corresponding fields for the specified ticker
    """

    items: list = [symbol]

    try:
        tk: dict = yf.Ticker(symbol)
        info: dict = tk.info

        for field in fields:
            items.append(get_value(info, field))
    # pylint: disable=broad-exception-caught
    except Exception:
        # pylint: enable=broad-exception-caught
        pass

    return items


def get_ticker_data(symbols: list, fields: list) -> pd.DataFrame:
    """
    Retrieve the specified fields from the input tickers
    """

    headers: list = ["Ticker"]
    for field in fields:
        headers.append(field)

    rows: list = []

    try:
        for symbol in symbols:
            rows.append(get_items(symbol, fields))

    # pylint: disable=broad-exception-caught
    except Exception:
        # pylint: enable=broad-exception-caught
        print("An error occured")

    df = pd.DataFrame(rows, columns=headers)

    return df


def get_ticker_weights(ticker_values: dict) -> pd.DataFrame:
    """
    Returns:
        ticker_weights
    """

    # Set up the structure
    rows: list = []
    headers: list = ["ticker", "weight"]

    # Calculate the portfolio value
    total_portfolio_value = sum(ticker_values.values())

    for ticker, value in ticker_values.items():
        weight = value / total_portfolio_value
        rows.append([ticker, weight])

    df = pd.DataFrame(rows, columns=headers)

    return df

get_ticker_data Takes a list of Ticker symbols and a list of fields. The fields are the items that we want to pick up from the Ticker -Info function of Yfinance. It uses the utility function get_value To pick up the requested value or 0 if the key is not available. Undoubtedly, the Python script can be simplified and made more pythonic.

In the worksheet we make a vector of tickers and a vector of fields and then we call on the Python function.

The result is a data frame with the values for the fields that we have requested (if available).

Portfolio weights

The second position get_ticker_weightsSimply calculates the weights of a list of Ticker symbols. The Python function requires that the input is a dictionary with symbols and their corresponding values. That is why we create one ticker_list Via r, since reticular r -lists converts to python dictionaries. The construction is somewhat mechanical and can certainly be improved by only changing the Python function inputs. With the ticker_list Made we can call on the Python function.

This returns the portfolio weights that we can use to display graphically. From here we were able to expand the Python code to calculate portfolio retours for the tickers for a certain period. We can then compare the returns with a benchmark and calculate Sharpe ratios for the components.

Pack

In this post we have seen how we can call Python scripts from R using the reticulate package. Although the setup is not completely trivial, it is worth taking into account the benefits of this approach: from Excel we have access to both the extensive ecosystems of R and Python, which in turn gives us a lot of flexibility when creating solutions. And that concludes this series of reports about the use of R in Excel. I hope this has been useful.

Related