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.
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.
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.
