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 MSN.com.  The exact web page is:

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

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:

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

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.

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

Sub GetWebData(SelectStock)

    Application.ScreenUpdating = False

    Sheets("Web Data").Activate

    Cells.ClearContents

    Range("A1").Activate

   

    With ActiveSheet.QueryTables.Add(Connection:= _

        "URL;http://www.msn.com/en-us/money/quoteslookup?SYMBOL=" & 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

 

    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

   

    ActiveCell.Offset(1, 0).Activate

    ActiveCell.Value = "TOTAL"

   

    'Determine Sum Range

    Range("GainBeginCell").Activate

    GainBeginCell = ActiveCell.Address

    ActiveCell.End(xlDown).Select

    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
    ActiveSheet.Select
    Range("PriceBeginCell").Offset(0, -1).Activate
    BeginRow = ActiveCell.Row
    ActiveCell.End(xlDown).Select
    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
        Rows(CurrRow).Select
        Selection.ClearContents
    End If
End Sub

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