Your 'Go To' for Custom Excel Solutions
excel-applications-blue2

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.

Share Trading Portfolio fixed.....new version released 3rd January, 2018

A recent change (Nov, 2017) in the Yahoo Finance website effectively disabled the function that returned prices for this free spreadsheet. The XLAutomation team has now developed a fix for this issue and it is now available for download. If you previously downloaded the Share Trading Portfolio, download it again to receive the new version.

Here is the Share Trading Portfolio in action...

Share-Trading-Portfolio-v2

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

Private Function strGetCurrentFinanceData(strURL As String) As String
'Extract the data from the Web URL

Dim strResult As String
Dim objRequest As WinHttp.WinHttpRequest

strGetCurrentFinanceData = ""

Set objRequest = New WinHttp.WinHttpRequest
With objRequest
.Open "GET", strURL, False
.setRequestHeader "Content-Type", "text/html"
.send
.waitForResponse (10)

strGetCurrentFinanceData = .responseText
End With

End Function

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 require Windows virtualisation.

Download Your Free Share Trading Portfolio

We will use the details entered  below to add you to our mailing list to receive the free download file and  latest news about free spreadsheets and product information from the XLAutomation Team. You will have the option to unsubscribe at any time. 

Another Free Spreadsheet by XLAutomation.

If you found this spreadsheet helpful please make a donation.

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.

11 Comments

  1. Scott Lindsay on April 11, 2018 at 5:06 am

    A change has been made to the Share Trading Portfolio to correct the Last Trade Price extracted. It should now download and update the Current Market Price which will be the Last Trade Price for the securities in your list. For those with prior versions you will need to download the latest version Share_Trade_Portfolio_XLAutomationv4

  2. John on April 10, 2018 at 1:53 am

    Hello,

    Downloading Canadian stocks form TSX. Values come across with wrong prices. At first I thought they were US prices but some don’t even come close. As well, some have the wrong description attached so it’s grabbing the wrong stock.

    Any help is appreciated.

    Thanks
    John

    • Scott Lindsay on April 10, 2018 at 2:14 pm

      John, can you provide some examples for us to check those wrong prices on the Canadian exchange….we will then investigate.

      • John on April 11, 2018 at 3:29 am

        Hi Scott,

        Parex Resources, ticker PXT, should be around $18-$19 and it comes in at $5.99 as the current price.
        Sun Life Insurance, ticker SLF, s/b around $51-$52 comes in at $70.86 on the SecurityCurrentDetails sheet but doesn’t show up in the MyPortfolio sheet.
        ZCL Composites, ticker ZCL, s/b around $12 but comes in at $35 as the current price.

        Thanks
        John

        • Scott Lindsay on April 11, 2018 at 4:48 am

          John,

          Yes I can see there are significant problems and inconsistencies in the data being returned for those securities. It also appears that other securities for other exchanges are also having problems. It may be related to changes in Yahoo’s website that is impacting the extract routines. We will look into this and release a fix if it can be resolved.

          Thanks
          Scott

          • Scott Lindsay on April 11, 2018 at 5:07 am

            Hi John,

            I have made a modification to the spreadsheet and it should now be finding the correct Last Trade Price for those stocks. You will need to download again to get the latest version Share_Trade_Portfolio_XLAutomationv4. Let me know if it does resolve the issue for your case.

            Thanks
            Scott

  3. Bernard on March 13, 2018 at 4:46 am

    What’s the code for LSE stocks ?….thanks

    • Scott Lindsay on March 13, 2018 at 6:55 am

      Bernard….you can use “L” in the Exchange column for LSE stocks

  4. paul on January 22, 2018 at 4:43 am

    Did not work for Hong Kong stocks.
    Please try Tencent 0700.HK
    https://finance.yahoo.com/quote/0700.HK?p=0700.HK

    paul
    BillyChiu.com

    • Scott Lindsay on January 22, 2018 at 10:00 pm

      Paul,
      Thanks for letting us know….as a workaround you can enter the following to find this stock using Share Trading Portfolio
      Enter – ‘0700 in the stock column
      Enter – HK in the exchange column
      Then “Update Prices” and it should return the values for that stock….the apostrophe character ‘ just before the first 0 tricks Excel into thinking that the 0700 is a text string and not a number.

  5. AlainD on January 11, 2018 at 6:56 am

    Thank you for fixing it, will try it again soon

Leave a Comment