Skip to content

Instantly share code, notes, and snippets.

@klahrich

klahrich/stock_perf.ipynb

Last active November 6, 2020 15:48
Show Gist options
  • Save klahrich/5c4311241de2aa67e43f486e6d546374 to your computer and use it in GitHub Desktop.
Save klahrich/5c4311241de2aa67e43f486e6d546374 to your computer and use it in GitHub Desktop.
Download ZIP
Display the source blob
Display the rendered blob
Raw
stock_perf.ipynb
{
"cells": [
{
"cell_type": "code",
"execution_count": 181,
"metadata": {
"jupyter": {
"source_hidden": true
}
},
"outputs": [],
"source": [
"import pandas as pd\n",
"from datetime import date, timedelta\n",
"import numpy as np\n",
"import numpy_financial as npf\n",
"from stock_perf import daily_cashflows, compute_irr, merge_portfolios, daily_quantity, ending_quantity\n",
"import yfinance as yf\n",
"import itertools\n",
"from collections import defaultdict"
]
},
{
"cell_type": "code",
"execution_count": 182,
"metadata": {
"jupyter": {
"source_hidden": true
}
},
"outputs": [],
"source": [
"start_date = date(2020, 1, 1)\n",
"end_date = date.today()\n",
"\n",
"accounts = ['regular', 'tfsa', 'rrsp']\n",
"start_values = {'regular': 65_165, 'tfsa': 44_641, 'rrsp':0}\n",
"#end_values = {'regular': 218_010, 'tfsa': 161_341}\n",
"\n",
"start_positions = {'regular': {'TD':0, 'DOC':0, 'CTS':0, 'ENGH':0, 'EWS':0, 'WELL':41740, \n",
" 'VERY':0, 'SHOP':0, 'ENB':0},\n",
"\n",
" 'tfsa': {'CTS': 0.0, 'DSG': 0.0, 'ENGH': 0.0, 'EWS': 0.0, 'WELL': 16030.0, \n",
" 'VERY': 0.0, 'SHOP': 0.0, 'CSU': 0.0, 'CRE': 48915.0, 'PWM': 16550.0},\n",
" \n",
" 'rrsp': {'FOOD':0, 'PTON':0, 'ENGH':0}}\n",
"\n",
"symbol_mapping = {'DOC':'DOC.V', 'CTS':'CTS.V', 'ENGH':'ENGH.TO', 'EWS':'EWS.V', 'WELL':'WELL.TO', 'VERY':'VERY.CN',\n",
" 'SHOP':'SHOP.TO', 'CSU':'CSU.TO', 'CRE':'CRE.V', 'PWM':'PWM.V', 'ENB':'ENB.TO', 'TD':'TD.TO', 'PFM':'PFM.V',\n",
" 'DSG':'DSG.TO', 'FOOD':'FOOD.TO' }\n",
"\n",
"symbols_usd = ['PTON']\n",
"usd_to_cad = 1.31"
]
},
{
"cell_type": "code",
"execution_count": 183,
"metadata": {
"jupyter": {
"source_hidden": true
}
},
"outputs": [],
"source": [
"filenames = {'regular':'transactions_regular.csv', 'tfsa':'transactions_tfsa.csv', 'rrsp':'transactions_rrsp.csv'}\n",
"transactions = {k:pd.read_csv(v) for (k,v) in filenames.items()}"
]
},
{
"cell_type": "code",
"execution_count": 184,
"metadata": {
"jupyter": {
"source_hidden": true
}
},
"outputs": [],
"source": [
"symbol_list = list(itertools.chain.from_iterable([d['symbol'].dropna().unique() for d in transactions.values()]))"
]
},
{
"cell_type": "code",
"execution_count": 185,
"metadata": {
"jupyter": {
"source_hidden": true
}
},
"outputs": [],
"source": [
"usd_to_cad = yf.download('CAD=X', start=start_date, progress=False).reset_index()\n",
"usd_to_cad.rename(columns={'Date':'date', 'Close':'exch_rate'}, inplace=True)\n",
"usd_to_cad = usd_to_cad[['date', 'exch_rate']]\n",
"usd_to_cad['date'] = usd_to_cad['date'].astype(str)"
]
},
{
"cell_type": "code",
"execution_count": 186,
"metadata": {
"jupyter": {
"source_hidden": true
},
"tags": []
},
"outputs": [],
"source": [
"ticker_values = {}\n",
"\n",
"for symbol in symbol_list:\n",
" ticker_data = yf.download(symbol_mapping.get(symbol, symbol), start=start_date, progress=False).reset_index()\n",
" ticker_values[symbol] = ticker_data[['Date', 'Close']].rename(columns={'Date':'date', 'Close':'close'})\n",
" ticker_values[symbol]['date'] = ticker_values[symbol]['date'].astype(str)"
]
},
{
"cell_type": "code",
"execution_count": 187,
"metadata": {
"jupyter": {
"source_hidden": true
}
},
"outputs": [],
"source": [
"doc = pd.read_csv('doc.vprice-history.csv')\n",
"doc = doc.iloc[0:(len(doc)-1)]\n",
"doc['month'] = doc['Time'].str[0:2]\n",
"doc['day'] = doc['Time'].str[3:5]\n",
"doc['year'] = doc['Time'].str[6:]\n",
"\n",
"doc['date'] = doc['year'] + '-' + doc['month'] + '-' + doc['day']\n",
"\n",
"doc.rename(columns={'Last':'close'}, inplace=True)\n",
"doc = doc[['date', 'close']]\n",
"\n",
"ticker_values['DOC'] = doc"
]
},
{
"cell_type": "code",
"execution_count": 188,
"metadata": {
"jupyter": {
"source_hidden": true
}
},
"outputs": [],
"source": [
"daily_quantities = defaultdict(dict)\n",
"\n",
"for account in accounts:\n",
" for symbol in set(symbol_list).intersection(start_positions[account]):\n",
" daily_quantities[account][symbol] = daily_quantity(transactions[account], symbol, start_date, end_date) \n",
" daily_quantities[account][symbol]['cum_quantity'] = np.cumsum(daily_quantities[account][symbol]['quantity']) + start_positions[account].get(symbol, 0)"
]
},
{
"cell_type": "code",
"execution_count": 189,
"metadata": {
"jupyter": {
"source_hidden": true
}
},
"outputs": [],
"source": [
"market_values = defaultdict(dict)\n",
"\n",
"for account in accounts:\n",
" for symbol in daily_quantities[account].keys():\n",
" market_values[account][symbol] = daily_quantities[account][symbol].merge(ticker_values[symbol], how='left', on='date').dropna()\n",
" market_values[account][symbol]['market_value'] = (market_values[account][symbol]['cum_quantity'] * market_values[account][symbol]['close'])\n",
" if symbol in symbols_usd:\n",
" market_values[account][symbol] = market_values[account][symbol].merge(usd_to_cad, how='left', on='date')\n",
" market_values[account][symbol]['market_value_us'] = market_values[account][symbol]['market_value']\n",
" market_values[account][symbol]['market_value'] = market_values[account][symbol]['market_value'] * market_values[account][symbol]['exch_rate']"
]
},
{
"cell_type": "code",
"execution_count": 190,
"metadata": {
"jupyter": {
"source_hidden": true
}
},
"outputs": [],
"source": [
"daily_flows = defaultdict(dict)\n",
"\n",
"for account in accounts:\n",
" for symbol in market_values[account].keys():\n",
" daily_flows[account][symbol] = daily_cashflows(transactions[account], start_date, end_date, symbol)"
]
},
{
"cell_type": "code",
"execution_count": 191,
"metadata": {
"jupyter": {
"source_hidden": true
}
},
"outputs": [],
"source": [
"historical = defaultdict(dict)\n",
"\n",
"for account in accounts:\n",
" for symbol in market_values[account].keys():\n",
" historical[account][symbol] = market_values[account][symbol].merge(daily_flows[account][symbol], how='left', on='date')\n",
" historical[account][symbol].rename(columns={'total_amount':'flow'}, inplace=True)"
]
},
{
"cell_type": "code",
"execution_count": 192,
"metadata": {
"jupyter": {
"source_hidden": true
}
},
"outputs": [],
"source": [
"for account in accounts:\n",
" for symbol in historical[account].keys():\n",
" historical[account][symbol]['gain'] = (historical[account][symbol]['market_value'] + historical[account][symbol]['flow']\n",
" - historical[account][symbol]['market_value'].shift(1)).fillna(0)"
]
},
{
"cell_type": "code",
"execution_count": 193,
"metadata": {
"jupyter": {
"source_hidden": true
},
"tags": []
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Lifetime gains:\n",
"\n",
"REGULAR\n",
"---\n",
" VERY $281.10\n",
" CTS $9,297.85\n",
" DOC $132,867.10\n",
" ENB $-1,576.68\n",
" SHOP $7,887.64\n",
" EWS $-2,869.75\n",
" ENGH $627.35\n",
" WELL $120,507.70\n",
" TD $-295.85\n",
"---\n",
"Total: $266,726.46\n",
"\n",
"TFSA\n",
"---\n",
" WELL $54,916.35\n",
" CTS $6,830.10\n",
" VERY $907.20\n",
" CSU $-212.70\n",
" CRE $-6,790.97\n",
" PWM $6,941.05\n",
" SHOP $41.78\n",
" EWS $875.25\n",
" ENGH $1,867.13\n",
" DSG $2,171.15\n",
"---\n",
"Total: $67,546.34\n",
"\n",
"RRSP\n",
"---\n",
" PTON $1,713.29\n",
" FOOD $152.55\n",
" ENGH $752.30\n",
"---\n",
"Total: $2,618.14\n",
"\n"
]
}
],
"source": [
"total = defaultdict(float)\n",
"\n",
"print('Lifetime gains:\\n')\n",
"for account in accounts:\n",
" print(f'{account.upper()}')\n",
" print('---')\n",
" for symbol in historical[account].keys():\n",
" gain = np.sum(historical[account][symbol]['gain'])\n",
" total[account] = total[account] + gain\n",
" print(f' {symbol}', '${:,.2f}'.format(gain))\n",
" print('---')\n",
" print(f'Total: ' + '${:,.2f}'.format(total[account])) \n",
" print()\n"
]
},
{
"cell_type": "code",
"execution_count": 201,
"metadata": {
"jupyter": {
"source_hidden": true
},
"tags": []
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"REGULAR\n",
"---\n",
"CTS\n",
" date gain\n",
"208 2020-10-30 -1716.001511\n",
"209 2020-11-02 2112.001133\n",
"210 2020-11-03 -1320.001888\n",
"211 2020-11-04 -1319.998741\n",
"212 2020-11-05 791.999245\n",
"\n",
"ENB\n",
" date gain\n",
"209 2020-10-30 10.698204\n",
"210 2020-11-02 -320.999184\n",
"211 2020-11-03 53.499184\n",
"212 2020-11-04 96.300163\n",
"213 2020-11-05 609.899673\n",
"\n",
"EWS\n",
" date gain\n",
"209 2020-10-30 0.000000\n",
"210 2020-11-02 -1399.998665\n",
"211 2020-11-03 -2800.001502\n",
"212 2020-11-04 1400.000751\n",
"213 2020-11-05 0.000000\n",
"\n",
"ENGH\n",
" date gain\n",
"209 2020-10-30 -852.749417\n",
"210 2020-11-02 -1337.001457\n",
"211 2020-11-03 1298.800583\n",
"212 2020-11-04 3161.047668\n",
"213 2020-11-05 105.050583\n",
"\n",
"WELL\n",
" date gain\n",
"209 2020-10-30 -1019.950877\n",
"210 2020-11-02 -249.998569\n",
"211 2020-11-03 2649.998665\n",
"212 2020-11-04 499.999523\n",
"213 2020-11-05 -149.998665\n",
"\n",
"TFSA\n",
"---\n",
"WELL\n",
" date gain\n",
"209 2020-10-30 -700.000525\n",
"210 2020-11-02 -124.999285\n",
"211 2020-11-03 1324.999332\n",
"212 2020-11-04 249.999762\n",
"213 2020-11-05 -74.999332\n",
"\n",
"CTS\n",
" date gain\n",
"208 2020-10-30 -1300.001144\n",
"209 2020-11-02 1600.000858\n",
"210 2020-11-03 -1000.001431\n",
"211 2020-11-04 -999.999046\n",
"212 2020-11-05 599.999428\n",
"\n",
"EWS\n",
" date gain\n",
"209 2020-10-30 0.000000\n",
"210 2020-11-02 -1649.998426\n",
"211 2020-11-03 -3300.001770\n",
"212 2020-11-04 1650.000885\n",
"213 2020-11-05 0.000000\n",
"\n",
"ENGH\n",
" date gain\n",
"209 2020-10-30 -797.999573\n",
"210 2020-11-02 -980.001068\n",
"211 2020-11-03 952.000427\n",
"212 2020-11-04 2316.998291\n",
"213 2020-11-05 77.000427\n",
"\n",
"DSG\n",
" date gain\n",
"209 2020-10-30 534.999847\n",
"210 2020-11-02 125.000000\n",
"211 2020-11-03 659.999847\n",
"212 2020-11-04 1520.000458\n",
"213 2020-11-05 79.998016\n",
"\n",
"RRSP\n",
"---\n",
"PTON\n",
" date gain\n",
"210 2020-10-30 0.000000\n",
"211 2020-11-02 0.000000\n",
"212 2020-11-03 -141.372178\n",
"213 2020-11-04 771.792454\n",
"214 2020-11-05 1082.869072\n",
"\n",
"FOOD\n",
" date gain\n",
"209 2020-10-30 0.00000\n",
"210 2020-11-02 0.00000\n",
"211 2020-11-03 -57.45004\n",
"212 2020-11-04 -41.99996\n",
"213 2020-11-05 251.99976\n",
"\n",
"ENGH\n",
" date gain\n",
"209 2020-10-30 0.000000\n",
"210 2020-11-02 59.199867\n",
"211 2020-11-03 197.200089\n",
"212 2020-11-04 479.949646\n",
"213 2020-11-05 15.950089\n",
"\n"
]
}
],
"source": [
"for account in accounts:\n",
" print(f'{account.upper()}')\n",
" print('---')\n",
" for symbol in historical[account].keys():\n",
" if(historical[account][symbol]['cum_quantity'].iloc[-1] > 0):\n",
" print(symbol)\n",
" print(historical[account][symbol][['date', 'gain']].tail())\n",
" print()"
]
}
],
"metadata": {
"kernelspec": {
"display_name": "datascience",
"language": "python",
"name": "datascience"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": 3
}
},
"nbformat": 4,
"nbformat_minor": 4
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment