Mark to Market Financial Report

msofficeshare logo

Requirement:  Visual Basic Knowledge

File to download:  Mark to Market.xlsm


Mark-to-Market is an important accounting concept.  It reflects the “fair value” of an asset or a portfolio based on the current market price.  “Fair value” is normally called “fair market value” and is a critical part of the “General Accepted Accounting Principles” (GAAP).  Market value of an asset or a portfolio may deviate from its original price (minus depreciation or amortization) due to many factors:

  • Market interest rates,
  • Change of risk levels or investors’ risk tolerance,
  • Liquidity,
  • Investors’ appetite,
  • Technology innovation, and
  • Competition, etc.


Mark-to-Market is critical when investors need to assess the performance of their portfolios, even though there could be only “unrealized” gains or losses at the moment.  A mark-to-market report is usually a “must-have” report among all financial or accounting report packages.  It gives a snapshot to investors how their portfolios are performing.   From there investors can plan for further actions like buy/sell/keep decisions.

Back in 2008 to 2010 after the US Subprime crisis occurred, the company that I worked for experiencing a high demand of mark-to-market reports.  Some investors previously did not ask for such report demanded it and were willing to pay good prices for producing such report, or have such report with higher frequency than previously requested.  Investors wanted to keep a close watch of their portfolios during such volatile period.

The Excel file that is for illustration and downloaded is for stock quotes.  Since many people own stocks, such file might be especially useful for small investors like us who want to know the performance of our investments.  The stock quotes get the information from  The exact web page is:

We need to plug in the stock symbol at the end of the statement.  For example, if we want to know the stock price of Microsoft (symbol = MSFT), we need to use the below URL:

Please note my program will obtain the latest stock prices from MSN web site, however, there is usually some delay on the MSN website with the current market prices.  MSN website has a disclaimer stating that the stock prices may be up to 15 minutes’ delay.  Therefore, the stock prices that are obtained by my program may also be up to 15 minutes’ delay from the current market prices.

The reason of using MSN website instead of Google or Yahoo is that MSN has a data format that is more suitable for importing into Excel.  When I tried to import Google and Yahoo, the imported data are messy and hence, difficult to grab the desired data.

That is how the imported data look like after importing into Excel.  The imported data are much the same as the presentation of the web page.

The below subroutine “GetWebData” pulls the stock data from MSN web site.

pic1 mark-to-market
pic2 mark-to-market

Sub GetWebData(SelectStock)

    Application.ScreenUpdating = False

    Sheets("Web Data").Activate




    With ActiveSheet.QueryTables.Add(Connection:= _

        "URL;" & SelectStock, Destination:=Range("$A$1"))


    End With

    Application.ScreenUpdating = True

End Sub

Most people own more than stocks from one company.  Therefore, we need to create a loop to import pricing data of multiple stocks.  Below are the VB codes of the main program.  A “Do” loop is used to read the stock symbol one-by-one into the subroutine “GetWebData” by passing the “SelectStock” variable to it.  The program will continue to loop until an empty cell is encountered.

This VB Module imports more data than I need.  I am only interested in the latest stock prices, so the program will only grab the stock prices, but ignore all other information.  The user can modify the program to obtain more data that the user is interested in.

The data from the website will be imported in the “Web Data” worksheet.  The latest price data will be copied and pasted as values in the “Stock Quotes” worksheet.

The main program also sums up the total unrealized gain/loss so the investors can have a good idea how the entire portfolio is performance in addition to just an individual stock.

Sub GetQuote()

    Dim SelectStock As String

    Dim PriceBeginCell As String, PriceEndCell As String

    Dim GainBeginCell As String, GainEndCell As String

    Dim LatestPrice As Double


    Call ClearPastInfo


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



        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


    ActiveCell.Offset(1, 0).Activate

    ActiveCell.Value = "TOTAL"


    'Determine Sum Range


    GainBeginCell = ActiveCell.Address


    GainEndCell = ActiveCell.Address


    Range(GainEndCell).Offset(2, 0).Activate

    ActiveCell.Value = "=SUM(" & GainBeginCell & ":" & GainEndCell & ")"

End Sub

Below is the interface of the program.  The interface is placed in the “Stock Quotes” worksheet.  The user needs to put in the stock symbol and historical data such as “Purchase Price” and “# of Share Bought”.  Afterwards, the user only needs to click the “Get Stock Data” button to initiate the VB Module.

Good luck to your investments and hope that you can make good money!

The VB Module can further be tailor-made to suit your needs.  If you know the websites, you can pull data by CUSIP, indices, and other parameters.

pic3 mark-to-market

Sub ClearPastInfo()
    Dim CurrRow As Integer, EndRow As Long
    Range("PriceBeginCell").Offset(0, -1).Activate
    BeginRow = ActiveCell.Row
    EndRow = ActiveCell.Row
    PriceColumn = Split(Range("PriceBeginCell").Address, "$")(1)
    GainColumn = Split(Range("GainBeginCell").Address, "$")(1)
    PriceBeginCell = PriceColumn & BeginRow
    PriceEndCell = PriceColumn & EndRow
    GainBeginCell = GainColumn & BeginRow
    GainEndCell = GainColumn & EndRow
    Range(PriceBeginCell, PriceEndCell).ClearContents
    Range(GainBeginCell, GainEndCell).ClearContents
    If EndRow < 1000 Then
        Range(GainEndCell).Offset(2, 0).Activate
        CurrRow = ActiveCell.Row
    End If
End Sub

The below VB Module is to delete past data before pasting the latest ones.