Explore
Groups
No topics yet. Start the conversation.
Description
The below description is supplied in free-text by the user
# %%%
import pandas as pd, sys, inspect
import yfinance as yf
from datetime import date, timedelta
from novem import Plot
from novem.colors import StaticColor as SC
from novem.table import Selector as S
MARKET_CONFIG = {
"commodities": {
"title": "Commodities",
"data": {
# Energy
'BZ=F': 'Oil Brent ($/bbl)',
'CL=F': 'Oil WTI ($/bbl)',
'TTF=F': 'Gas TTF (€/MWh)',
# Metals
'GC=F': 'Gold COMEX ($/oz)',
'SI=F': 'Silver COMEX ($/oz)',
'PL=F': 'Platinum COMEX ($/oz)',
'HG=F': 'Copper COMEX ($/lb)',
'ALI=F': 'Aluminum COMEX ($/t)',
'TIO=F': 'Iron Ore CFR ($/t)',
# Agriculture / Softs
'CC=F': 'Cocoa ICE ($/t)',
'KC=F': 'Coffee ICE (¢/lb)',
'SB=F': 'Sugar ICE (¢/lb)',
'ZW=F': 'Wheat CBOT (¢/bu)',
'ZC=F': 'Corn CBOT (¢/bu)',
'ZS=F': 'Soy CBOT (¢/bu)',
},
"border": [0, 3, 9, -1], # line breaks after energy, some metals, and end
},
"cryptos": {
"title": "Cryptocurrencies",
"data": {
'BTC-USD': 'Bitcoin (USD)',
'ETH-USD': 'Ether (USD)',
'XRP-USD': 'Ripple (USD)',
'ADA-USD': 'Cardano (USD)',
'SOL-USD': 'Solana (USD)',
'LTC-USD': 'Litecoin (USD)',
'DOT-USD': 'Polkadot (USD)',
'DOGE-USD': 'Dogecoin (USD)'
},
"border": [0, 2, -1], # example: break after top-5 and at end
},
"eq_markets": {
"title": "Equity Markets",
"data": {
# US
'^GSPC': 'S&P 500 (USD)',
'^IXIC': 'Nasdaq (USD)',
'^RUT': 'Russell 2000 (USD)',
# America other
'^GSPTSE': 'TSX (CAD)', # Canada
'^MXX': 'IPC (MXN)', # Mexico
'^BVSP': 'Bovespa (BRL)', # Brazil
# Eurozone
'^GDAXI': 'DAX (DE, EUR)', # Germany
'^FCHI': 'CAC 40 (FR, EUR)', # France
'^IBEX': 'IBEX 35 (ES, EUR)', # Spain
# Europe other
'^FTSE': 'FTSE 100 (GBP)', # UK
'^SSMI': 'SMI (CHF)', # Switzerland
'OBX.OL': 'OBX 25 (NOK)', # Norway
'^OMX': 'OMX 30 (SEK)', # Sweden
'^OMXC25': 'OMX 25 (DKK)', # Denmark
# Asia-Pacific
'^N225': 'Nikkei 225 (JPY)', # Japan
'^HSI': 'Hang Seng (HKD)', # Hong Kong
'000001.SS': 'Shanghai Comp (CNY)', # China
'^BSESN': 'BSE Sensex (INR)', # India
'^AORD': 'ASX 200 (AUD)' # Australia
},
"border": [0, 3, 6, 9, 14, -1],
},
}
HORIZONS = {'1d': 1, '1w': 5, '1m': 22, '1y': 252}
YAHOO_URL = 'https://finance.yahoo.com/quote/'
START_DATE = (date.today() - timedelta(days=2*365)).isoformat() # Two years ago
tuesday_to_saturday = date.today().weekday() in (1,2,3,4,5)
if tuesday_to_saturday: lookback_days = 1
else: lookback_days = 3
LAST_BDAY = date.today() - timedelta(days=lookback_days)
def estimate_close_yesterday(ticker: str) -> float:
"""Estimate yesterday's closing price by taking the last available price from intraday data."""
# Hack since yfinance does not always provide yesterday's close price
df = yf.download(ticker, period="5d", interval="4h",auto_adjust=True)['Close']
df_last_bday = df[df.index.date == LAST_BDAY]
if df_last_bday.empty:
return pd.NA
else:
return df_last_bday.iloc[-1].item()
def add_estimated_close_prices(df: pd.DataFrame) -> pd.DataFrame:
if not LAST_BDAY in df.index:
# Add a new row for LAST_BDAY with estimated NA prices first
df_last_bd = pd.DataFrame(index=[LAST_BDAY], columns=df.columns)
df_last_bd.index.name = df.index.name
df = pd.concat([df, df_last_bd])
for ticker in df.columns:
if pd.isna(df.at[LAST_BDAY, ticker]):
estimated_price = estimate_close_yesterday(ticker)
df.at[LAST_BDAY, ticker] = estimated_price
return df
def format_number(x: float) -> str:
if x < 100:
return f"{x:,.2f}"
elif x < 1000:
return f"{x:,.1f}"
elif x < 100_000:
return f"{x:,.0f}"
elif x < 1_000_000:
return f"{x/1000:,.1f}k" # show in thousands
else:
return f"{x/1_000_000:,.1f}M" # millions
def enrich_and_take_latest(g):
g = g.sort_values('value_date')
g["t"] = pd.to_datetime(g["value_date"]).dt.date
latest = g.iloc[-1:].copy() # Latest value
# If LAST_BDAY is latest date use that as latest
if LAST_BDAY in g['t'].values:
latest = g[g['t'] == LAST_BDAY].copy()
notice = ""
elif max(g['t']) < LAST_BDAY:
latest = g.iloc[-1:].copy()
notice = f"{max(g['t']).strftime('(%-d/%-m)')}"
else:
latest = g.iloc[-1:].copy()
notice = f"{max(g['t']).strftime('(%-d/%-m)')}"
stdev = g['price'].pct_change().std() # Stdev
changes = {k: g['price'].pct_change(v).iloc[-1] for k, v in HORIZONS.items()}
zscores = {f'z_{k}': changes[k] / (stdev * (v**0.5)) for k, v in HORIZONS.items()}
return latest.assign(**changes, **zscores, notice=notice)
# %%
for section, config in MARKET_CONFIG.items():
DATA = config['data']
BORDER = config['border']
TITLE = config['title']
# (1/3) Fetch data from Yahoo Finance and convert to long format
tickers = list(DATA.keys())
df_wide = yf.download(list(DATA.keys()), start=START_DATE, end=date.today(),auto_adjust=True)['Close']
df_wide.index = pd.to_datetime(df_wide.index).date
#df_wide = add_estimated_close_prices(df_wide)
df_wide.index.name = 'value_date'
df_long = df_wide.stack().rename('price').reset_index().rename(columns={'Ticker': 'ticker'})
# (2/3) DATA ENRICHMENT
df_latest = df_long.groupby('ticker', group_keys=False).apply(enrich_and_take_latest, include_groups=True).reset_index(drop=True)
df_latest['Description'] = df_latest['ticker'].map(DATA)
t_latest = max(df_latest['value_date'])
# Add a stamp (d/m) for rows where value_date is not the latest date
#df_latest['Description'] = df_latest['Description'] + ' ' + df_latest['notice']
# Add a html link to Yahoo finance per security
df_latest['url'] = df_latest['ticker'].apply(lambda t: f'[{t}]({YAHOO_URL}{t})')
# Sort the ticker, the way they are sorted in the Commodities list
df_latest['ticker'] = pd.Categorical(df_latest['ticker'], categories=DATA.keys(), ordered=True)
df_latest = df_latest.sort_values('ticker')
df_latest = df_latest.set_index('Description')
df_latest['price'] = df_latest['price'].apply(format_number)
# (3/3) NOVEM PLOT
visible_cols = [ 'price'] + list(HORIZONS.keys()) + ['url']
data = df_latest[visible_cols]
plt = Plot(section) # Initiate the plot
plt.type = 'table' # Set type to table (waiting to set to mtable until later for faster rendering)
plt.data = data # Pipe the data to novem
print(plt.url) # Show the url of the plot
plt.cell.align = '''
: 1: >
: 0 <
0 2:-2 -
'''
plt.cell.border = ''
for b in BORDER:
plt.cell.border += f'{b} : b 1 gray-500\n'
plt.cell.padding= '''
: 1: l 4
: :-2 r 4
0 : t 2
'''
plt.cell.format = '' # Reset all cell formats
plt.cell.format += S(data.loc[:, 'price'], ' ', data)
plt.cell.format += S(data.loc[:, '1d'], ',.1%', data)
plt.cell.format += S(data.loc[:, '1w':], ',.0%', data)
# Color each value based on whether it is 'special' i.e. z-score > 1
plt.colors.type = 'ix'
plt.colors = '' # Reset
for horizon in HORIZONS:
z_col = f'z_{horizon}'
col_index = data.columns.get_loc(horizon) + 1
plt.colors += S(df_latest.loc[df_latest[z_col] > 1, [horizon]], SC('bg', 'green-100'), data, col_index)
plt.colors += S(df_latest.loc[df_latest[z_col] < -1, [horizon]], SC('bg', 'red-100'), data, col_index)
plt.colors += S(df_latest.loc[df_latest[z_col] > 1, [horizon]], SC('fg', 'green-700'), data, col_index)
plt.colors += S(df_latest.loc[df_latest[z_col] < -1, [horizon]], SC('fg', 'red-700'), data, col_index)
plt.name = TITLE + ' ' + LAST_BDAY.strftime('%d %b %Y')
plt.title = ''
plt.caption = f'''{TITLE}. Colors highlights moves greater than one standard deviation. Close prices as of {LAST_BDAY.strftime('%d %b %Y')}. Source: Yahoo! Finance, calculations by novem. '''
plt.type = 'mtable' # Set plot type to mtable at the end (ensures rendering is only done once, faster)
plt.shared += 'public' # Make the graph public
plt.x
# Include this code in description
code_block = f'''```python
{inspect.getsource(sys.modules[__name__])}
```'''
plt.description = code_block
# %%
```