How to Easily Manage Your Stock Portfolio with Excel

If you are stepping into stock trading, you would have already realized that it is very important to keep a track of your stock portfolio. There are several online portals that allow you to track your portfolio but you would also have to pay a fee for that. With Excel build your own and customized stock portfolio tracker.

Download Now

If you want to start to use the software as soon as possible, then you can:

Download the Software Now

Otherwise, if you want to DIY, you can read the contents below.

Let’s Prepare the GUI

We need two sheets in the tool. The first sheet should be renamed as “Portfolio” and the second sheet should be named as “Database”. As shown in the image, on the sheet “Portfolio” create a field to upload historical data. Also, create a table to maintain the portfolio. Using shapes, create a button and name it as “Refresh”

Let’s prepare the database

Save the historical data in a CSV file. Add the path of the CSV file under the header “Upload Stock Data” and click “Import”. The database sheet would now have the historical data. Update your portfolio by adding values under “Stock Symbol”, “Buy Date”, “Buy Price” and “Quantity”

Let’s make it functional

Copy the script into a new module. Attach the macro “Import_Stock_Data” to the button “Import” and the macro “Refresh_Price” to the button “Refresh”. Add a date under the header “Latest Date” and hit the “Refresh” button. Macro will now read the database to fetch the close price on the specified date and it will also identify the profit/loss made for that particular stock.

Sub Import_stock_Data()
    With Sheets("Database").QueryTables.Add(Connection:= _
    "TEXT;" & Sheets("Portfolio").Range("B9").Value _
    , Destination:=Sheets("Database").Range("$A$2"))
    .Name = "HistoricalData"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With

Call Module1.Refresh_Date
Call Module1.Refresh_Price    
End Sub

Sub Refresh_Date()
    Dim lr As Long
    Dim v_date As Date
    Dim r As Long
    lr = Sheets("Portfolio").Range("J" & Rows.Count).End(xlUp).Row
    For r = 9 To lr
        v_date = DateValue("Jan 19, 1950")
        Dim lrindb As Long
        Dim rindb As Long
        lrindb = Sheets("Database").Range("A" & Rows.Count).End(xlUp).Row
        For rindb = 2 To lrindb
            If Sheets("Database").Range("A" & rindb).Value = Sheets("Portfolio").Range("J" & r).Value Then
                If CDate(Sheets("Database").Range("B" & rindb).Value) > CDate(v_date) Then
                    v_date = Sheets("Database").Range("B" & rindb).Value
                    Sheets("Portfolio").Range("T" & r).Value = Sheets("Database").Range("B" & rindb).Value
                End If
            End If
        Next rindb
    Next r
End Sub

Sub Refresh_Price()
    Dim lr As Long
    Dim r As Long
    lr = Sheets("Portfolio").Range("J" & Rows.Count).End(xlUp).Row
    For r = 9 To lr
        Dim lrindb As Long
        Dim rindb As Long
        lrindb = Sheets("Database").Range("A" & Rows.Count).End(xlUp).Row
        For rindb = 2 To lrindb
            If (Sheets("Database").Range("A" & rindb).Value = Sheets("Portfolio").Range("J" & r).Value) And (Sheets("Database").Range("B" & rindb).Value = Sheets("Portfolio").Range("T" & r).Value) Then
                'MsgBox "Yes"
                Sheets("Portfolio").Range("V" & r).Value = Sheets("Database").Range("F" & rindb).Value
            End If
        Next rindb
    Next r
End Sub

How does it work?

The macro imports the historical data into database from the CSV file. When user mentions a new data, the close price is read from the database and the portfolio gets updated. If you are not able to import the historical data into the database, either the CSV file would have been corrupted or you might be working on a damaged xlsx or xls file. If the CSV file was corrupted, save historical data in a txt file and upload it into the database.

Author Introduction:

Nick Vipond is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including fix Word and outlook recovery software products. For more information visit www.datanumen.com