Modules and Functions
This module runs the main application. The process goes like this:
- Setup: update platform and security referential, cash balances -> cache on DB
- Setup: process new transactions
- Market data: collect the latest (including intra-day) NAV of supported stocks, ETFs and mutual funds with existing holdings -> cache on DB
- Market data: collect the latest (including intra-day) FX rates -> cache on DB
- Market data: collect historical EOD USDHKD exchange rates -> cache on DB
- Market data: collect historical EOD market data of supported stocks, ETFs and mutual funds including those already sold -> cache on DB
- Calculations: compute portfolio summary and IRR%s -> cache on DB
- Visualisations: load cached data from DB and plot various charts
This module handles the following:
- Connects to MongoDB
- Platform and securities referential
- Transactions processing
- Weighted average cost calculation (when selling)
- Handling of uninvested cash balances
InsertPlatform(PlatformName, Currency)
This function inserts a record to the Platform table.
InsertSecurity(db, SecurityCode, SecurityAssetClass, SecurityAssetType, SecurityCategory, SecurityName, SecurityCcy, SecurityFXCode, BBGPriceMultiplier, FundHouse, YahooFinanceTicker)
This function inserts a record to the Security table.
InsertTransaction(db, Platform, Date, Type, BBGCode, CostInPlatformCcy, PriceInSecurityCcy, Quantity, Dividend=None, Comment=None)
This function inserts a record to the Transactions table.
UpdateLatestFXrates()
This function collects the latest FX rates (via Yahoo Finance API) of a list of predefined currency pairs. NOTE: Previously, the data was collected via Bloomberg Terminal API, hence the data field names.
UpdateLatestBullionRates()
This function collects the latest precious metal rates (via Yahoo Finance API) of a list of predefined commodities.
InitialSetup()
This function runs the initial setup of the following:
- Platforms
- Securities
InsertHistTransactions(start_date=datetime.datetime(2020,12,1)
This function inserts transactions from the specified start date (old records are removed first).
ConnectToMongoDB()
This function establishes the connection to the MongoDB.
GetPlatformCurrency(platform)
This function gets the currency of the specified platform.
GetSecurityCurrency(security)
This function gets the currency of the specified security.
GetWeightedAvgCost(TransactionDate, BBGCode, Quantity, Platform)
This function gets the weighted average cost for the specified security and platform, returned in the specified platform's currency.
GetWeightedAvgCostPerUnitInSecCcy(bbgcode, platform)
This function gets the weighted average cost per unit for the specified security and platform, returned in the security's currency using the latest FX conversion rates.
GetAllTransactions()
This function gets all the transactions on the DB, returned as a pandas data frame.
GetSecurities()
This function gets all the full list of security referential on the DB, returned as a pandas data frame.
GetYahooFinanceTicker(bbgcode)
This function converts a Bloomberg ticker code (unique identifier - supports all instruments) into Yahoo Finance ticker (does not support most mutual funds), in order to call the Yahoo Finance APIs.
GetTransactionsETFs()
This function gets the list of transactions of those instruments supported by Yahoo Finance, i.e. stocks, ETFs, and very limited mutual funds. NOTE: The naming of this function is because only ETFs were in scope originally.
GetETFDataDataRanges(bbgcode)
This function gets the start and end dates for the security (supported by Yahoo Finance), so that historical prices can be collected for specified dates only.
GetBankAndCashBalances()
This function gets the separate data source (XLSX) for bank, cash, and gold balances.
- Yahoo Finance API
- Get historical NAV
- Get historical FX rates
- Historical NAV processing, cache on MongoDB
UpdateLastNAV()
This function gets the latest NAV for the list of predefined securities, and stores it on the same file.
ProcessHistoricalMarketData(bbgcode=None, platform=None, start_date=None)
This function processes the historical market data for the list of supported instruments:
- collects the historical data for relevant date ranges (based on transactions)
- fills dates of market holidays with the previous prices
- saves the data on MongoDB
ProcessHistoricalUSDHKD()
This function collects the historical USDHKD rate and stores it on MongoDB.
ProcessHistoricalSPX()
This function collects the historical S&P 500 closing price and stores it on MongoDB.
Ccypair2YFTicker(ccypair)
This function converts a currency pair into Yahoo Finance ticker.
GetLatestPrice(ticker, display_log=False)
This function gets the latest price of the specified security.
GetHistoricalData(bbgcode=None, start_date=None)
This function collects the cached historical data of the specified security from MongoDB.
GetHistoricalUSDHKD()
This function collects the cached historical USDHKD rate from MongoDB (for converting into base currency HKD).
GetHistoricalSPX()
This function collects the cached historical S&P 500 closing price from MongoDB (for bench-marking portfolio returns against).
- FX conversions
ConvertFX(ccy_source, ccy_target)
This function returns the FX conversion rate, using latest live rate from Yahoo Finance API (to be called during initialisation only).
GetFXRate(target_ccy, original_ccy)
This function returns the FX conversion rate, using cached data from MongoDB (available for predefined list of currencies only).
ConvertTo(target_ccy, original_ccy, original_amount)
This function converts the amount from one currency to another, using cached data from MongoDB (available for predefined list of currencies only).
- Latest NAV processing (both Yahoo Finance API and manual input of unsupported mutual funds)
ProcessLastNAV()
This function processes the latest NAV (both from Yahoo Finance API for stocks & ETFs, as well as manual input source for mutual funds), gets the latest FX conversion rates from security currency to platform currency, and stores it on MongoDB.
GetLastNAV()
The function collects the cached latest NAV data along with FX conversion rates from MongoDB (to feed into the portfolio summary computation).
- Portfolio summary, inc. uninvested cash
- Realised and unrealised PnL
- Adjustment: 1 fund -> allocation to 3 asset classes
GetPortfolioSummary()
This function computes the portfolio summary; it also adjusts for Allianz Income & Growth by splitting this fund into 3 separate asset classes
GetPortfolioSummaryIncCash()
This function computes the portfolio summary, including cash and gold balances.
CalcPortfolioSummaryAndCacheOnDB()
This function calls the computation of portfolio summary (original, adjusted), and portfolio summary including cash/gold, and cache them on MongoDB.
GetHistoricalRealisedPnL()
This function returns the historical realised PnL.
GetPortfolioSummaryFromDB(summary_type='Original')
This function collects the cached portfolio summary from MongoDB.
GetPnLUnrealised()
This function returns the unrealised PnL.
TopHoldings()
This function returns the top holdings in the consolidated portfolio.
- Internal Rate of Return (money-weighted): suitable measurement for an individual investor (emphasizes on impacts of in/out flows)
- Yahoo Finance API (historical prices, FX rates)
- Historical cost vs valuation
- Calculates returns of benchmark (S&P 500)
CalcIRRAndCacheOnDB()
This function calculates the IRR (Internal Rate of Return) of a predefined list of date ranges for the entire consolidated portfolio, and caches them on MongoDB.
CalcIRR(platform=None, bbgcode=None, period=None)
This function calculates the IRR (Internal Rate of Return) of the specified platform, ticker, and date range.
GetIRRFromDB()
This function collects the cached IRR from MongoDB.
CalcPortfolioHistoricalValuation(platform=None, bbgcode=None, start_date=None)
This function calculates the historical valuation of the specified platform, ticker, and start date - this is used in the plot over time of cost vs valuation.
CalcPortfolioHistoricalCost(platform=None, start_date=None, base_ccy='HKD')
This function calculates the historical investment cost of the entire portfolio (or specified platform) - this is used in the plot over time of cost vs valuation..
GetSPXReturns()
This function returns the S&P 500 performance for a predefined list of date ranges - this is used in the bench-marking of the portfolio performance.
- Portfolio Performance (IRR) vs Benchmark (S&P 500)
- Cost vs Latest Valuation
- Realised PnL over time, breakdown by dividends & trading PnL
- Portfolio Composition
- Asset Allocation
- Currency Exposure
- Top Holdings
- Product Type Breakdown
- Holdings by Fund House
DisplayPnL()
This function displays a summary of realised and unrealised PnL in plain text format in the console
DisplayPortfolioSummary()
This function displays a summary of the portfolio in plain text format in the console
DisplayReturnPct()
This function displays the portfolio returns, bench-marked against S&P 500 returns.
PlotPortfolioComposition()
This function plots a donut chart of the portfolio composition by predefined categories
PlotAssetAllocationCurrencyExposure(group_small_items=0.01)
This function plots a donut chart of asset allocation and currency exposure; it can group items below a threshold into a single group.
PlotPortfolioCompositionBy(by='SecurityType', inc_cash=True)
This function plots a donut chart of the portfolio broken down by a specified data field, e.g. Product Type / Fund House.
PlotCostvsVal(period='6M', platform=None)
This function plots the historical investment cost against valuation.
PlotTopHoldings()
This function plots the top holdings in the portfolio, displayed in both HKD value and % of portfolio total.
PlotRealisedPnLOverTime(period='6M')
This function plots a stacked bar chart of realised PnL (dividends vs capital gains) over the specified period.
PlotPerformanceOfHoldings(period='3M')
This function plots the performance of top holdings over a specified period.
PlotLeadersAndLaggers(period=None, top_n=5)
This function plots the gainers and losers over a specified period.
- PAD reporting
- Transactions list
- Holdings summary (balance b/f and c/f)
GetTransactionList(DateFrom, DateTo)
This function generates the list of transactions based on input start and end dates - to be pasted into PAD declaration form.
GetHoldingsSummary(DateFrom, DateTo)
This function generates the list of portfolio holdings including balance b/f and balance c/f - to be pasted into the PAD declaration form.
- handling of date range periods
GetStartDate(period=None)
This function returns the start date based on the input period (YTD, 1W, 1M, 3M, 6M, 1Y, 3Y, 5Y).