Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'd like to get the current course on the NYSE for ONE company (e.g. Coca Cola = COKE).

    In the same way (or maybe that is different???); I'd like to get the exchange rate (Dollar to Euro).

    I don't need trends, just one value for each of these two.

    I've seen a lot of code on the net but all is quite large and complex and is doing much more than the very basic functionality I need.

    Anyone with simple code to get these two values in an Excel cell if I click a button ???

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Perhaps you can use the free Excel 2003/2002 Add-in: MSN Money Stock Quotes.


  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I did some more searching on the Internet and, combining pieces of code available there and some slight changes by myself I was able to come up with the code below, it works!. All I need now is a similar (???) way to get the exchange rate $ to Euro:

    Dim Site As String, Ticker As String, StockPrice As String, oXMLHttp
    Site = "
    http://finance.yahoo.com/d/quotes.csv?s="
    Ticker = "COKE"
    '
    On Error GoTo SomethingWrong
    '
    Set oXMLHttp = CreateObject("MSXML2.ServerXMLHTTP")
    oXMLHttp.Open "GET", Site & Ticker & "&f=l1", False
    oXMLHttp.send ""
    '
    StockPrice = Trim(oXMLHttp.responseText)
    Range("Stock") = Mid(StockPrice, 1, Len(StockPrice) - 2)
    GoTo Done
    SomethingWrong:
    Range("Stock") = 0
    Done:
    Set oXMLHttp = Nothing





  4. #4
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts
    OK, more work done and this works too (it's dangerous as this assumes the HTML-code on Yahoo is pretty stable for this type of query but -for now- it works.

    Dim Site As String, Cur1 As String, Cur2 As String, Result As String, oXMLHttp
    Cur1 = "Dollar"
    Cur2 = "Euro"
    Site = "
    http://search.yahoo.com/search?p=1+" & Cur1 & "+in+" & Cur2
    '
    On Error GoTo SomethingWrong
    '
    Set oXMLHttp = CreateObject("MSXML2.ServerXMLHTTP")
    oXMLHttp.Open "GET", Site, False
    oXMLHttp.send ""
    '
    Result = Trim(oXMLHttp.responsetext)
    Dim i As Integer, j As Integer
    i = InStr(10000, Result, Cur2)
    j = InStr(i - 30, Result, "=")
    Range("ExRate") = Mid(Result, j + 1, i - j - 1)
    GoTo Done
    SomethingWrong:
    Range("ExRate") = 0
    Done:
    Set oXMLHttp = Nothing



Posting Permissions

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