Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Web Query (Excel 2003)

    Hi,

    I would like to use a web query to download table relating to a number of
    companies. This query will be run using the unique company identifier (ISIN)
    which point to a cell where the identifier is stored.
    How can I get excel to run down a column containing unique identifiers and
    automatically pass it to the webquery.


    Thanks in advance

    cheers, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Web Query (Excel 2003)

    Does one web page contain info for all companies, or do you have to visit a different web page for each company?

    If the former, does one table contain all the information, or is there a separate table for each company?

  3. #3
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Web Query (Excel 2003)

    Hi Hans,

    I will need to visit several web pages for the information and each company will have its table.....

    The the procedure is to go to the sites. Select the menu companies, Then search by
    the identifier, ISIN, to get the announcement items regarding a company and then
    download it to excel.

    For example, if I need to extract the announcements from this url : http://www.investegate.co.uk/
    I have attached a sample list of ISINs for the London Stock Exxhange.

    Appreciate your assistance.

    cheers, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Web Query (Excel 2003)

    Doesn't the site offer services to automate this kind of stuff, like direct URL's to specific information? Send them an email, sometimes you're surprised with detailed info on how to do things.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Web Query (Excel 2003)

    I'd follow Jan Karel Pieterse's suggestion first. If you started running multiple queries to a website in quick succession, they might think you were attacking them.

  6. #6
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Web Query (Excel 2003)

    Hi Hans and Jan,
    Thank you for the pointer, but no response from the web. I wanna use Excel to access the web page and pull
    the announcements portion into Excel. The program run like this:

    I have the isin nos or company names in the column A
    Once the data is downloaded, Excel checks to see if it says No
    announcements. If there is an announcements, copy it to a sheet under
    the company's name.
    Then runs the query again with A2 and so on....

    If you have a better idea, I would like to hear from you. My main aim
    is to track these companies announcements on dividend, rights issues,
    tender offers, bonus, meetings, mergers and earnings.

    It is important that Excel can automatically pass the identifiers /
    company name's to the web query and pull the relevant data, in this case Announcements.

    thanks.

    cheers, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Web Query (Excel 2003)

    I cannot help you with this, sorry.

  8. #8
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Web Query (Excel 2003)

    Thanks for your response.

    cheers, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Web Query (Excel 2003)

    I have a page on my site on creating a parameter webquery:

    http://www.jkp-ads.com/articles/webquery.asp
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Web Query (Excel 2003)

    Jan,
    Thanks for the info. I know that there are some programs out there, but I want to learn by building this myself. Using the VBA code on your web page, How does one locate the connection string to connect, say to the following :
    Sub WebQ()
    Dim lRange As Long
    Dim l As Long
    Dim sWord As String
    lRangeCount = Cells(Rows.Count, "A").End(xlUp).Row
    Range("A1").Select
    sWord = ActiveCell.Value
    For l = 0 To lRange
    ActiveCell.Offset(1, 0).Select
    If sWord = "" Then Exit For
    Dim X As ActiveSheet.QueryTables
    Dim strURL As String
    Dim strResults As String
    Set oX = X.Add("URL;http://www.euronext.com/WHAT GOES IN HERE....../

    I am stuck at : lRangeCount = Cells(Rows.Count, "A").End(xlUp).Row, this supposed th loop thru column A
    and also at the URL , the url which I copied shows :
    http://www.euronext.com/pricesearch/0,5375,1732_
    6204224_____89221,00.html?requestComesFromSearchBo xParameter=
    true&instrumentType=5&instrumentSubType=&equitiesC hoice=4&mep=
    89221&belongsToList=market_14&bondType=503&issuer= &couponType=
    &tradingType=&NAME_FIELD1=&MIN_FIELD1=&MAX_FIEL D1= &NAME_FIELD2=
    &MIN_FIELD2=&MAX_FIELD2=

    I am lost as to what to look for. <img src=/S/confused.gif border=0 alt=confused width=15 height=20> . Appreciate your assistance in this.

    Thanks in advance.

    cheers, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Web Query (Excel 2003)

    Hi,

    I'd change this part of the code to start with:

    Sub WebQ()
    Dim lRange As Long
    Dim l As Long
    Dim sWord As String
    lRangeCount = Cells(Rows.Count, "A").End(xlUp).Row
    Range("A1").Select
    sWord = ActiveCell.Value
    For l = 0 To lRange
    ActiveCell.Offset(1, 0).Select
    If sWord = "" Then Exit For
    Dim X As ActiveSheet.QueryTables
    Dim strURL As String
    Dim strResults As String
    Set oX = X.Add("URL;http://www.euronext.com/WHAT GOES IN HERE....../

    To:

    Sub WebQ()
    Dim lRange As Long
    Dim l As Long
    Dim sWord As String
    lRangeCount = Cells(Rows.Count, "A").End(xlUp).Row
    Range("A1").Select
    sWord = ActiveCell.Value
    For l = 0 To lRange
    ActiveCell.Offset(1, 0).Select
    If sWord = "" Then Exit For
    Dim X As QueryTable
    Dim strURL As String
    Dim strResults As String
    Set oX = Activesheet.querytables.Add("URL;http://www.euronext.com/WHAT GOES IN HERE....../
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Web Query (Excel 2003)

    Shouldn't that either be:

    <code>
    Dim oX As QueryTable
    </code>


    or

    <code>
    Set X = Activesheet.querytables.Add("URL;http://www.euronext.com/WHAT GOES IN HERE....../
    </code>
    Legare Coleman

  13. #13
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Web Query (Excel 2003)

    Well spotted Legare!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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