← Functions

Mark to Market Financial Report

How to build a Mark-to-Market stock portfolio report in Excel using a VBA module that pulls live stock prices from the web and calculates unrealized gains and losses.

Mark-to-Market is an important accounting concept that reflects the "fair value" of an asset or portfolio based on current market price. It is a critical part of Generally Accepted Accounting Principles (GAAP). Market value may deviate from original cost due to many factors:

  • Market interest rates
  • Changes in risk levels or investors' risk tolerance
  • Liquidity
  • Investors' appetite
  • Technology innovation
  • Competition

A mark-to-market report is a "must-have" in any financial or accounting report package. It gives investors a snapshot of how their portfolios are performing, supporting buy/sell/hold decisions.

How It Works

The Excel model pulls stock quotes from MSN.com using a web query. The URL format is:

https://www.msn.com/en-us/money/quoteslookup?SYMBOL=MSFT

MSN's data format is more suitable for importing into Excel than Google or Yahoo. Note that MSN may show stock prices with up to a 15-minute delay.

VBA Modules

GetWebData — pulls stock data from MSN for a single stock symbol:

Sub GetWebData(SelectStock)
    Application.ScreenUpdating = False
    Sheets("Web Data").Activate
    Cells.ClearContents
    Range("A1").Activate
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;https://www.msn.com/en-us/money/quoteslookup?SYMBOL=" & SelectStock, _
        Destination:=Range("$A$1"))
        ' ... query table configuration ...
    End With
    Application.ScreenUpdating = True
End Sub

GetQuote — loops through all stock symbols, calls GetWebData for each, grabs the latest price, and calculates unrealized gain/loss:

Sub GetQuote()
    Dim SelectStock As String
    Dim LatestPrice As Double

    Call ClearPastInfo
    Range("BeginCell").Offset(1, 0).Activate

    Do
        SelectStock = ActiveCell.Value
        Call GetWebData(SelectStock)
        LatestPrice = Range("D4").Value

        Sheets("Stock Quotes").Activate
        ActiveCell.Offset(0, 1).Value = LatestPrice
        ActiveCell.Offset(0, 4).FormulaR1C1 = "=(RC[-3]-RC[-2])*RC[-1]"
        ActiveCell.Offset(1, 0).Activate
    Loop Until IsEmpty(ActiveCell.Value) = True

    ' Sum total unrealized gain/loss
    ActiveCell.Offset(1, 0).Activate
    ActiveCell.Value = "TOTAL"
    ' ... SUM formula written dynamically ...
End Sub

ClearPastInfo — clears previous price and gain/loss data before refreshing.

Usage

In the "Stock Quotes" worksheet, enter each stock symbol along with historical data (purchase price and number of shares). Click the Get Stock Data button to run the module. The program loops through each symbol, imports the latest price, and calculates the unrealized gain/loss for each holding and the portfolio total.

The module can be extended to pull data by CUSIP, indices, or other parameters from other financial data websites.