Build a Dividend & Portfolio Tracker in Excel by using Python [Free Template]
Language:
In this tutorial, I will show you the Excel Dividend & Portfolio Tracker I have built for you. All you need to do is to enter the Ticker Symbol, the number of shares you own as well as your average purchase price.
The Excel Spreadsheet is pulling the financial data from Yahoo Finance by using Python. It is not only limited to US stock. You could return any stock, cryptocurrency, forex, mutual fund & ETF data which is available on Yahoo Finance.
In this video, I will show you:
- how to use this template
- how you can build your own portfolio tracker from scratch
- and last but not least, how to customize this template to your needs and liking
📝 Resources:
You can download the Dividend & Portfolio Template for free here:
Download Here
👩💻 Source Code:
from enum import Enum # Standard Python Library import time, os, sys # Standard Python Library import xlwings as xw # pip install xlwings import pandas as pd # pip install pandas from yahoofinancials import YahooFinancials # pip install yahoofinancials # ============================== # Purpose: # Returning stock, cryptocurrency, forex, mutual fund, commodity futures, ETF, # and US Treasury financial data from Yahoo Finance & export it MS EXCEL # # Hints: # In case you want to adjust/change/add more information to the worksheet, # make sure to do your respective adjustments in the following: # a) class Column(Enum): change/add the column-number/name # b) adjust the dictonary "new_row" in the function "pull_stock_data()" # ============================== print( """ ============================== Dividend & Portfolio Overview ============================== """ ) class Column(Enum): """ Column Name Translation from Excel, 1 = Column A, 2 = Column B, ... """ long_name = 1 ticker = 2 current_price = 5 currency = 6 conversion_rate = 7 open_price = 8 daily_low = 9 daily_high = 10 yearly_low = 11 yearly_high = 12 fifty_day_moving_avg = 13 twohundred_day_moving_avg = 14 payout_ratio = 19 exdividend_date = 20 yield_rel = 21 dividend_rate = 22 def timestamp(): t = time.localtime() timestamp = time.strftime("%b-%d-%Y_%H:%M:%S", t) return timestamp def clear_content_in_excel(): """Clear the old contents in Excel""" if LAST_ROW > START_ROW: print(f"Clear Contents from row {START_ROW} to {LAST_ROW}") for data in Column: if not data.name == "ticker": sht.range((START_ROW, data.value), (LAST_ROW, data.value)).options( expand="down" ).clear_contents() return None def convert_to_target_currency(yf_retrieve_data, conversion_rate): """If value is not available on Yahoo finance, it will return None""" if yf_retrieve_data is None: return None return yf_retrieve_data * conversion_rate def get_coversion_rate(ticker_currency): """ Calculate the coversion rate between ticker currency & desired output currency (TARGET_CURRENCY) Return: conversion rate """ if TARGET_CURRENCY == "TICKER CURRENCY": print(f"Display values in {ticker_currency}") conversion_rate = 1 return conversion_rate conversion_rate = YahooFinancials( f"{ticker_currency}{TARGET_CURRENCY}=X" ).get_current_price() print( f"Conversion Rate from {ticker_currency} to {TARGET_CURRENCY}: {conversion_rate}" ) return conversion_rate def pull_stock_data(): """ Steps: 1) Create an empty DataFrame 2) Iterate over tickers, pull data from Yahoo Finance & add data to dictonary "new row" 3) Append "new row" to DataFrame 4) Return DataFrame """ if tickers: print(f"Iterating over the following tickers: {tickers}") df = pd.DataFrame() for ticker in tickers: print(f"~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~") print(f"Pulling financial data for: {ticker} ...") data = YahooFinancials(ticker) open_price = data.get_open_price() # If no open price can be found, Yahoo Finance will return 'None' if open_price is None: # If opening price is None, append empty dataframe (row) print(f"Ticker: {ticker} not found on Yahoo Finance. Please check") df = df.append(pd.Series(dtype=str), ignore_index=True) else: try: try: long_name = data.get_stock_quote_type_data()[ticker]["longName"] except (TypeError, KeyError): long_name = None try: yield_rel = data.get_summary_data()[ticker]["yield"] except (TypeError, KeyError): yield_rel = None ticker_currency = data.get_currency() conversion_rate = get_coversion_rate(ticker_currency) new_row = { "ticker": ticker, "currency": ticker_currency, "long_name": long_name, "conversion_rate": conversion_rate, "yield_rel": yield_rel, "exdividend_date": data.get_exdividend_date(), "payout_ratio": data.get_payout_ratio(), "open_price": convert_to_target_currency( open_price, conversion_rate ), "current_price": convert_to_target_currency( data.get_current_price(), conversion_rate ), "daily_low": convert_to_target_currency( data.get_daily_low(), conversion_rate ), "daily_high": convert_to_target_currency( data.get_daily_high(), conversion_rate ), "yearly_low": convert_to_target_currency( data.get_yearly_low(), conversion_rate ), "yearly_high": convert_to_target_currency( data.get_yearly_high(), conversion_rate ), "fifty_day_moving_avg": convert_to_target_currency( data.get_50day_moving_avg(), conversion_rate ), "twohundred_day_moving_avg": convert_to_target_currency( data.get_200day_moving_avg(), conversion_rate ), "dividend_rate": convert_to_target_currency( data.get_dividend_rate(), conversion_rate ), } df = df.append(new_row, ignore_index=True) print(f"Successfully pulled financial data for: {ticker}") except Exception as e: # Error Handling exc_type, exc_obj, exc_tb = sys.exc_info() fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1] print(exc_type, fname, exc_tb.tb_lineno) # Append Empty Row df = df.append(pd.Series(dtype=str), ignore_index=True) return df return pd.DataFrame() def write_value_to_excel(df): if not df.empty: print(f"~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~") print(f"Writing data to Excel...") options = dict(index=False, header=False) for data in Column: if not data.name == "ticker": sht.range(START_ROW, data.value).options(**options).value = df[ data.name ] return None def main(): print(f"Please wait. The program is running ...") clear_content_in_excel() df = pull_stock_data() write_value_to_excel(df) print(f"Program ran successfully!") show_msgbox("DONE!") # --- GET VALUES FROM EXCEL # xw.Book.caller() References the calling book # when the Python function is called from Excel via RunPython. wb = xw.Book.caller() sht = wb.sheets("Portfolio") show_msgbox = wb.macro("modMsgBox.ShowMsgBox") TARGET_CURRENCY = sht.range("TARGET_CURRENCY").value START_ROW = sht.range("TICKER").row + 1 # Plus one row after the heading LAST_ROW = sht.range(sht.cells.last_cell.row, Column.ticker.value).end("up").row sht.range("TIMESTAMP").value = timestamp() tickers = ( sht.range(START_ROW, Column.ticker.value).options(expand="down", numbers=str).value )