Your 'Go To' for Custom Excel Solutions

Share Trading Portfolio

A Free spreadsheet by the XLAutomation development team to assist share traders. It includes an automated feature that gives you the ability to extract share price data from another website for use in portfolio analysis

Share Trading Portfolio...automated share price extract

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.

'Now perform the Web Query
Set myWebQueryRange = ThisWorkbook.Worksheets("Yahoo.ComStockData").Range("YahooComStockDataWebQuery")
intCountWebQueries = intCountWebQueries + 1
With myWebQueryRange.QueryTable
    .Connection = _
    "URL;" & 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.

Download Your Free Share Trading Portfolio

Enter your details below to receive your free spreadsheet. 

Another Free Spreadsheet by XLAutomation.

If you found this spreadsheet helpful please make a donation.



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.