Mark to Market Financial Report

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:

https://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:

https://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;https://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.

More Posts