Row Zero is now in public beta

← Back

yfinance Tutorial in a Spreadsheet

2023-09-02 // Nick End, Founder

What is yfinance?

yfinance is a popular Python library that provides free access to financial data made available by Yahoo Finance. The library was deveoped by Ran Aroussi after yahoo deprecated their API in 2017. More information about yfinance can be found at at the pypi yfinance page.

How to write yfinance Commands

In the following post, we will show you how to use the most common yfinance Python commands that pull financial data into to a spreadsheet where it can be analyzed, graphed, and monitored.

How to install yfinance

Using yfinance in Row Zero makes it easy to execute commands. Unlike using yfinance in Jupyter or a code editor, you do not need to worry about running a Python environment. Row Zero takes care of all the comlexity. Open a free Row Zero workbook by clicking here. Next open the Row Zero code window using the right side menu. Then import the yfinance Library with the one line command below and execute the code window by pressing shift + enter or clicking the 'run' button.

import yfinance as yf
import pandas as pd

Row Zero code window

Import historical stock data for one Ticker from yfinance

To start, define a python function that expects a ticker symbol argument and returns the stock's pricing history. The yfinance 'Ticker()' function is used in this example and is one of the most popular yfinance functions. Run the code with with shift + enter. There are a number of additional arguments that can be used with history(). Review the appendix at the end of this post for more details.

import yfinance as yf
import pandas as pd

def STOCK(ticker):
    return yf.Ticker(ticker).history(period="max")

Now use that function in the spreadsheet by typing the function with a ticker in any cell and hitting enter. =stock()

You can also type a ticker in a cell and reference the cell in the formula.

=stock(A0)

ticker in cell

The following data table will be returned using both methods, whih shows Tesla's stock price data since it's IPO

Tesla stock data table

Modify Timeframe and Columns

Modify Timeframe

Update the python command and specify a different period to import the last X days of data.

def STOCK(ticker):
    return yf.Ticker(ticker).history(period="100d")

Another easy option is to use Row Zero's filters by clicking on the drop down at the top of a column and entering a filter by date. yfinance filter by date

Modify Columns

Once the data has been imported to Row Zero, right click and select 'manage columns' to remove columns of data that aren't needed. yfinance stock manage columns

Import Historical Data for Multiple Tickers from yfinance

To import data for multiple tickers, use the download() command in yfinance. The paramters for download() can be found at the end of this post.

Use the following command to write a function that imports the closing price for a number of stock tickers. In this example, it is important to set the groupby parameter to either column or Ticker based on the desired structure of the data table.

def get_stock_prices(tickers):
    data = yf.download(list(tickers), group_by='column', period="1000d", interval='1d')
    return data[['Close', 'Volume']]

get_stock_prices can be used in Row Zero to reference cells with tickers of interest. See the images below for an example. Get stock prices spreadsheet function

After hitting enter, the function will return the following data table: Get sock prices data table

Import Company Fundamentals with yfinance

Fundamentals for one ticker from yfinance

Yfinance also makes it easy to gather fundamental data for various companies using get_financials(). Using the Python function below, pass in a ticker and the function will pull the fundamental data from yfinance into the spreadsheet.

def get_financials(ticker):
    tck = yf.Ticker(ticker)
    return tck.get_financials()

get_financials() in spreadsheet

Fundamentals for multiple tickers from yfinance

It may be more helpful to pull fundamental data for multiple companies at once. Do to so, use the function below which expects multiple tickers and will return results from yfinance for all tickers in the selected range.

def get_multiple_financials(tickers):
    tickers = [yf.Ticker(ticker) for ticker in tickers]
    dfs = [] # list for each ticker's dataframe
    for ticker in tickers:
        # get each financial statement
        pnl = ticker.financials
        bs = ticker.balancesheet
        cf = ticker.cashflow

        # concatenate into one dataframe
        fs = pd.concat([pnl, bs, cf])

        # make dataframe format nicer
        # Swap dates and columns
        data = fs.T
        # reset index (date) into a column
        data = data.reset_index()
        # Rename old index from '' to Date
        data.columns = ['Date', *data.columns[1:]]
        # Add ticker to dataframe
        data['Ticker'] = ticker.ticker
        dfs.append(data)
    df = pd.concat(dfs, ignore_index=True)
    df = df.T.drop_duplicates().T
    df = df.set_index(['Ticker','Date'])
    return df

get_multiple_fundamentals() in spreadsheet

Options Data from yfinance

Get puts for one ticker with yfinance

It is easy to get options data from yfinance using options.puts and options.calls. Below is a function that passes yfinance a ticker and returns the puts for that ticker.

def get_puts(ticker):
    tck = yf.Ticker(ticker)
    options = tck.option_chain()
    return options.puts

get puts from yfinance

Get calls for one ticker with yfinance

The following functions passes a ticker to yfinance and returns the calls associated with the ticker.

def get_calls(ticker):
    tck = yf.Ticker(ticker)
    options = tck.option_chain()
    return options.calls

get calls from yfinance

Get institutional holders for one ticker from yfinance

It is also easy to find the institutional holders of a company's stock using institutional_holders in yfinance.

def get_institutional_holders(ticker):
    tck = yf.Ticker(ticker)
    return tck.institutional_holders

get institutional holders from yfinance

Pros/Cons of yfinance

yfinance is a great free tool for analyzing financial data but there are a few pros and cons to consider.

Pros:

  • yfinance is free. There are a few other free financial data sources but they are less widely used. Paid data sources are very expensive (Bloomberg costs $24,000/year)
  • User-friendly. yfinance is easy to install and use. Pypi reports several hundred thousand installs each month.
  • Valuable data. yfinance exposes data for strategic investing decisions.

Cons:

  • Dependencies. yfinance can break if Yahoo Finance ever changes the format of their site. If this were to happen, it is likely the yfinance library would be updated to remedy the situation but access to data may incur a short disruption.
  • Data frequency. The highest frequency data is minute level, which makes yfinance a less suitable option for real time trading.
  • Reliability. Paid data sources typically offer higher fidelity data from more reliable access points, like terminals and APIs.

Summary

Yfinance is a great free resource for pulling and analyzing stock information. It's easy to pull stock prices, fundamentals, options data, and institutional holders among many other options. There are pros and cons to using yfinance. The pros center on it's ease of use and its free price tag. The cons mainly center on reliability and data frequency.

Appendix

This section includes additional information about paramters for popular functions available in the yfinance python library. Reference these lists to expand upon the history() and download() functions provided above. There is also additional information on the pypi yfinance page.

history() Parameters

The following are all the parameters for the history() command in yfinance.

    :Parameters:
        period : str
            Valid periods: 1d,5d,1mo,3mo,6mo,1y,2y,5y,10y,ytd,max
            Either Use period parameter or use start and end
        interval : str
            Valid intervals: 1m,2m,5m,15m,30m,60m,90m,1h,1d,5d,1wk,1mo,3mo
            Intraday data cannot extend last 60 days
        start: str
            Download start date string (YYYY-MM-DD) or _datetime.
            Default is 1900-01-01
        end: str
            Download end date string (YYYY-MM-DD) or _datetime.
            Default is now
        prepost : bool
            Include Pre and Post market data in results?
            Default is False
        auto_adjust: bool
            Adjust all OHLC automatically? Default is True
        back_adjust: bool
            Back-adjusted data to mimic true historical prices
        proxy: str
            Optional. Proxy server URL scheme. Default is None
        rounding: bool
            Round values to 2 decimal places?
            Optional. Default is False = precision suggested by Yahoo!
        tz: str
            Optional timezone locale for dates.
            (default data is returned as non-localized dates)
        timeout: None or float
            If not None stops waiting for a response after given number of
            seconds. (Can also be a fraction of a second e.g. 0.01)
            Default is None.
        **kwargs: dict
            debug: bool
                Optional. If passed as False, will suppress
                error message printing to console.

Download() Paramters

The following are all the parameters for the download() command in yfinance. Each paramater can be used to specify changes in the data returned from each function.

    # Download yahoo tickers
    :Parameters:
        tickers : str, list
            List of tickers to download
        period : str
            Valid periods: 1d,5d,1mo,3mo,6mo,1y,2y,5y,10y,ytd,max
            Either Use period parameter or use start and end
        interval : str
            Valid intervals: 1m,2m,5m,15m,30m,60m,90m,1h,1d,5d,1wk,1mo,3mo
            Intraday data cannot extend last 60 days
        start: str
            Download start date string (YYYY-MM-DD) or _datetime.
            Default is 1900-01-01
        end: str
            Download end date string (YYYY-MM-DD) or _datetime.
            Default is now
        group_by : str
            Group by 'ticker' or 'column' (default)
        prepost : bool
            Include Pre and Post market data in results?
            Default is False
        auto_adjust: bool
            Adjust all OHLC automatically? Default is False
        actions: bool
            Download dividend + stock splits data. Default is False
        threads: bool / int
            How many threads to use for mass downloading. Default is True
        proxy: str
            Optional. Proxy server URL scheme. Default is None
        rounding: bool
            Optional. Round values to 2 decimal places?
        show_errors: bool
            Optional. Doesn't print errors if True
        timeout: None or float
            If not None stops waiting for a response after given number of
            seconds. (Can also be a fraction of a second e.g. 0.01)