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:
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
Pentapeptide-3 is an anti-wrinkle ingredient that is very potent. It has been proven to reduce wrinkling by 49%. The primary use of Pentapeptide-3 was for the decrease in Crow’s feet wrinkles, but it has now been found to be effect on other facial wrinkles. Studies have found Pentapeptide-3 to be effective in anti-aging problems. It is been tested in creams and has similar results to Botox in partially paralyzing the muscles and reducing fine lines and wrinkles.Pentapeptide-3 stimulates the “matrix” layers in the skin — primarily collagen and fibronectin. When stimulated, the skin produces more collagen. Loss of collagen is what leads to thinning skin and the wrinkling of newly inelastic skin.