Share Trading Portfolio
Do you use a spreadsheet to record your trades ? Do you have to manually update your open positions with the latest prices to calculate your portfolio performance ? We have developed another free spreadsheet that will automatically scan your list of trades and return the current market price from Yahoo Finance and recalculate the profit/loss for each position and the total trading portfolio.

Here is the Share Trading Portfolio in action...


Key Code Components

We provide a sample Excel workbook at the bottom of this post with all the required code to extract current market price from Yahoo Finance for your listed securities. The key code components to achieve the extract are shown below with some explanation. If you have questions or comments on our approach please let us know in the comments at the end of the post.

Web Query to extract price data for listed securities
'Now perform the Web Query
Set myWebQueryRange = ThisWorkbook.Worksheets("Yahoo.ComStockData").Range("YahooComStockDataWebQuery")
intCountWebQueries = intCountWebQueries + 1
With myWebQueryRange.QueryTable
    .Connection = _
    "URL;http://download.finance.yahoo.com/d/quotes.csv?s=" & strSecurityList & "&f=%20" & strParameters
    .WebSelectionType = xlSpecifiedTables
    .WebFormatting = xlWebFormattingNone
    .WebTables = "1"
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
End With


Sample Excel Workbook

Download the sample workbook and update it with your trade details, click on "Update Prices" to search for and return the current market price for each trade.

Note: This workbook has been tested on Windows running Office 2013. No testing on MAC has been performed. It may work for a MAC environment but will need to be verified by others.


Click on the "Email me my Free Spreadsheet" button below to get a copy of this sample workbook. 


Our Promise: "We will only use your contact details to share the latest news, free spreadsheets and product information from the XLAutomation team. We will never share your contact details with anyone else."

Tell us what you think...

Thanks to the trading community who have downloaded our spreadsheet. We would love to know how useful you've found this solution, and if you think we could include anything else to make it even better! Leave a comment and let us know.