CODEWITHSUNDEEP
pythonpandaspandas-resampleohlc
Jon 310
Jon 310

Reputation: 11

How to resample OHLC data with multiple stocks in index?

I haven't been able to find anything too similar to this I have OHLC data pulled from y-finance for multiple stocks. This results in a multi-index of columns of OHLC data and stock names

Python Script '''

import requests
import pandas as pd
import numpy as np
import yfinance as yf
from datetime import datetime, timedelta
N_DAYS_AGO = 15
now = datetime.now()
today = datetime(now.year,now.month,now.day, now.hour)
n_days_ago = today - timedelta(days=N_DAYS_AGO)

df = yf.download(['SPY','TLT'],  start=n_days_ago, end=now, interval = "60m")  #no error with 1 stock

ohlc_dict = {
    'Adj Close':'last',
    'Open':'first',
    'High':'max',
    'Low':'min',
    'Close':'last',
    'Volume':'sum'
    }

df_sample = df.resample('W-FRI', closed='left').agg(ohlc_dict)
df_sample  #error with 2 stocks

'''

The code above works without a single stock but fails when there are multiple stocks/ multi index columns.

I've tried stacking and unstacking but haven't found a good way to resample this data. What's the simplest path forward here?

Upvotes: 1

Views: 367

Answers (1)

Cyberjoe
Cyberjoe

Reputation: 3

This code downloads data from Yahoo Finance for multiple symbols, and resamples the data from daily to weekly.

# Import libraries
import yfinance as yf
import pandas as pd

# Define symbols/tickers
symbols = ['SPY', 'TLT', 'MSFT']

# Download data from Yahoo Finance
mydf = yf.download(symbols, start='2020-01-01', 
                   end='2023-01-01', group_by = 'ticker')

# Get DataFrame with MultiIndex of "Ticker" and "Date"
mydf = mydf.stack(level = 0, future_stack = True).swaplevel().sort_index()

# Define pandas resample inputs
ohlc_dict = {
    'Open':'first',
    'High':'max',
    'Low':'min',
    'Close':'last',
    'Adj Close':'last',
    'Volume':'sum'
    }

# Loop through symbols and create resampled DataFrame
mydf_resampled = pd.DataFrame()
for symbol in symbols:
    cont = mydf.loc[symbol].resample('W-FRI', closed = 'left').agg(ohlc_dict)
    cont['Ticker'] = symbol
    mydf_resampled = pd.concat([mydf_resampled, cont])

# Create MultiIndex for resampled DataFrame
mydf_resampled = mydf_resampled.reset_index()
mydf_resampled = mydf_resampled.set_index(['Ticker', 'Date'])
mydf_resampled

To select a specific "Ticker", use mydf_resampled.loc['SPY']

Upvotes: 0

Related Questions