Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    New Lounger
    Join Date
    Nov 2015
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy value from a internet page

    Hi,

    Greetings!

    I want to copy the value (current market value of a share) from an internet page and paste in an excel cell.

    Whenever I open the excel file, this value should be replaced with the most current value from the internet page.

    For example, in the attached file, I want the value in cell E2 to be copied from the website mentioned in cell G2 (as 572.85).

    What is the most easiest way for the above? I searched the forum before posting here, but was quite confused with few of the threads (sorry I am not an expert in excel).

    Thanks in advance.

    BR,
    Fahim
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Fahim,

    Welcome to the Lounge as a new poster.

    The only way I know to do what you want is via the Data tab -- From Web:

    WebData.JPG

    You enter the web address then click the Go button to the right.
    This will bring up your page and you can then select the appropriate yellow/black arrow for the data you want. In this case you have to select the one in the upper left corner for the entire page. Then click Import.

    Select the location, I'd suggest New Worksheet A1.

    You can then find the Current price and reference that cell on your main sheet.

    You will need to refresh the page every time you load the workbook this can be done with a auto_open macro:
    Code:
    Option Explicit
    
    Sub Auto_Open()
    
    '*** Refresh Stock Quote Query ***
    
        Application.ScreenUpdating = False
        ActiveWorkbook.RefreshAll
    
    End Sub    'Update_Quotes()
    Of course this can get out of hand if you have a lot of individual stocks/mutual funds. What I use is ighome.com to setup an easily to import page.
    stocks.JPG
    On this website I get a yellow arrow for just that section so it imports a lot less data and I get all my stocks on one page.

    Your worksheet adjusted: Share_Value_Test.xlsm

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,179
    Thanks
    47
    Thanked 983 Times in 913 Posts
    You could lash out on an Excel add-in ($15).
    http://spearian.com/products/spearian-for-excel

    Or go the free route.
    https://code.google.com/p/finansu/

    cheers, Paul

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    mdfahiem,

    Here is some code that will load your website, scrape the value of the current stock, then place the value in cell E2. Run the code by clicking the button on the worksheet. Be patient as the website loads into memory.

    You will need to reference 2 additional libraries:
    Microsoft Internet Controls
    Microsoft HTML Object Library

    Let me know if you need instructions on how to do that, it is really quite simple. Let me know If I scraped the wrong value, it was an educated guess since the values have changed since your posting.

    HTH,
    Maud

    Place in a standard module:
    Code:
    Sub GetTraderPrice()
    '--------------------------------
    'DECLARE AND SET VARIABLES
    Dim IExplore As InternetExplorer
    Dim html As HTMLDocument
    Dim TraderPrice As IHTMLElement
    Set IExplore = New InternetExplorer
    '--------------------------------
    'LOAD WEBSITE
    Status.Show
    IExplore.Visible = False
    IExplore.Navigate "http://economictimes.indiatimes.com/wipro-ltd/stocks/companyid-12799.cms"
    Do
        DoEvents
    Loop Until IExplore.ReadyState = 4
    '--------------------------------
    'RETRIEVE VALUE
    Status.Label1.Caption = "Retrieving Value"
    Set html = IExplore.Document
    Set TraderPrice = html.getElementById("nseTradeprice")
    Range("E2") = TraderPrice.outerText
    '--------------------------------
    'CLEANUP
    Unload Status
    Set IExplore = Nothing
    Set html = Nothing
    End Sub
    Attached Files Attached Files

  5. The Following User Says Thank You to Maudibe For This Useful Post:

    RetiredGeek (2015-11-02)

  6. #5
    New Lounger
    Join Date
    Nov 2015
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you RetiredGeek, Paul and Maud for your guidance.

    I tried all the options and found Maud's as the one which satisfies my requirement.

    Maud please help me for the below:
    1. I don't know what Microsoft Internet Controls & Microsoft HTML Object Library means and where I can use them.
    2. Please send me the steps on how to do this (I need them to add more shares in excel).

    Thanks again to all.

    BR,
    Fahim

  7. #6
    New Lounger
    Join Date
    Nov 2015
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks all again.

    I was able to copy the macro and paste and make the required changes. It's working well if I have 2 to 5 shares, hope it will work well if I add more shares.

    BR,

  8. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    mdfahiem,

    I take it that you were able to add the 2 vba references needed to run the code in your project? If you need to add to your project instead of working out of the revised workbook I provided, then let me know and I will give you the simple steps to do so.

    The code will only work for one stock value. If you want to collect multiple values:
    1. Extract multiple Stock values on the same web page- you duplicate the following lines for each additional stock while changing the variable name, looking up the element ID for the field in the source code, and changing it in the line code.
    Code:
    Dim TraderPrice As IHTMLElement
    '....
    Set TraderPrice = html.getElementById("nseTradeprice")
    Range("E2") = TraderPrice.outerText
    2. Extract Stocks from multiple pages- you will need to loop through the code and insert new URL (array variable) with each loop. You will also need to find the element ID for each value field and inset it in the code line.

    Please let me know which scenario or a combination of both that applies.

    Maud

  9. #8
    New Lounger
    Join Date
    Nov 2015
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Maudibe View Post
    mdfahiem,

    I take it that you were able to add the 2 vba references needed to run the code in your project? If you need to add to your project instead of working out of the revised workbook I provided, then let me know and I will give you the simple steps to do so.

    The code will only work for one stock value. If you want to collect multiple values:
    1. Extract multiple Stock values on the same web page- you duplicate the following lines for each additional stock while changing the variable name, looking up the element ID for the field in the source code, and changing it in the line code.
    Code:
    Dim TraderPrice As IHTMLElement
    '....
    Set TraderPrice = html.getElementById("nseTradeprice")
    Range("E2") = TraderPrice.outerText
    2. Extract Stocks from multiple pages- you will need to loop through the code and insert new URL (array variable) with each loop. You will also need to find the element ID for each value field and inset it in the code line.

    Please let me know which scenario or a combination of both that applies.

    Maud
    Hi Maud,

    How are you doing?

    I have copied the vba (sent by you) in my original file and whenever I hit the update button, another excel file is opening (Share_Value_Test_Revised.xlsm). I don't want this file to open.

    Right now, I copied the button and editing the vba as per my requirement, how to create a new button - please explain step wise.

    I would like to know the steps required for the option 2 (Extract Stocks from multiple pages).

    Many thanks for your kind guidance.

    BR,
    Fahim

  10. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Fahim,

    When you copied the button, you also copied the call to the macro which is "Share_Value_Test_Revised.xlsm!GetTraderPrice" . So, when you click on the button, it is looking for a macro located in my revised spreadsheet

    Fahim1.png

    Right click on the button > Assign Macro... > Select GetTraderPrice > OK. The button will now point to the macro in your workbook and not the one in mine.

    Do you have the URL links for your other stocks? I will help you with the code to get those values.

    Maud

  11. #10
    New Lounger
    Join Date
    Nov 2015
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi again,

    I am trying to create a new macro as below:
    1. Insert a image as Rectangle.
    2. Assign the Macro (macro is copied from your previous post, editing done as per my requirement).
    3. Click on the rectangle.
    4. Message is shown as “Compile error: User-defined type not defined”
    5. If I click ok, then it is highlighting “Dim IExplore As InternetExplorer”

    Please guide me how to create a new macro.

    Thanks again.

    BR,
    Fahim

  12. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    You will need to reference 2 additional libraries:
    Microsoft Internet Controls
    Microsoft HTML Object Library
    The error is caused by not having the above libraries referenced. If you need the simple instructions on how to add the references, please let me know or google it. I would suggest to post the modified macro so that can review and head off and unexpected behavior.

    Maud

  13. #12
    New Lounger
    Join Date
    Nov 2015
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Maudibe View Post
    The error is caused by not having the above libraries referenced. If you need the simple instructions on how to add the references, please let me know or google it. I would suggest to post the modified macro so that can review and head off and unexpected behavior.

    Maud
    Could you please explain the instructions?

    Thanks a lot for your kind support.

    BR,
    Fahim

  14. #13
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Fahim,

    1. From the excel spreadsheet, press Alt-F11 to open the VB Editor.
    2. Click on Tools > References...

    fahiem1.png

    3. There will be some libraries already checked. Scroll down and place a check mark next to Microsoft Internet Controls and Microsoft HTML Object Library.

    fahiem2.png

    4. Click OK > Close the VB Editor (red "X") > Save

    The code should now run

    HTH,
    Maud

  15. #14
    New Lounger
    Join Date
    Nov 2015
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Maud,

    Greetings!

    I am facing problem, when I start adding more shares. Please find the attached file. I have 4 shares, for first 2 shares, macros are working well. For last 2 shares, it is showing error as below:
    "Cannot run the macro 'Share_Value.xlsm!GetTradePrice'. The macro may not be available in this workbook or all macros may be disabled."

    Also is it possible to have one box in excel file which will automatically update all the values, instead of having 4 different boxes (one box for each share)?

    I have noticed that whenever I open this excel file, my laptop will performance drops and it behaves very slow. Is there any specific reason for this?

    Thanks in advance.

    BR,
    Fahim
    Attached Files Attached Files

  16. #15
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    "Cannot run the macro 'Share_Value.xlsm!GetTradePrice'. The macro may not be available in this workbook or all macros may be disabled."
    Fahiem,

    Nice job amending the code.

    The reason you are getting the message for the last two stocks is because 'Share_Value.xlsm!GetTradePrice' macro does not exist. You need to set assign the Morepen button to the 'GetTradePriceMor' macro and the SPYL button to the 'GetTradePriceSPYL' macro.

    To have one button run all the stocks you can:
    1. (Easiest) Create a macro that the one button points to. In the macro, list the four subroutines. When the control macro runs, it will run the 4 macros successively.
    2. Have one code called by one button which loops through the cells F2 through F5 and fins the values.

    HTH,
    Maud

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •