j844929
Active Member
- Joined
- Aug 18, 2002
- Messages
- 423
Hi,
I'm trying to import all of the EUROSTOXX 50 spot prices from the following URL:
http://www.finanzen.net/aktien/realtimekurse.asp?inindex=11
What's happening is that the live updating Bid and Ask prices in the bottm table aren't displayed in the spreadsheet after running the web query. I assume this is probably because their properties are different to static data. I've tried all sorts of thing like changing properties in the web query etc, all to no avail. I've also tried the following script, but when it pastes to the spreadsheet, the code tends to fall over:
Can anyone help me improve the vba above or is anyone able to point me in the right direction for the vba to import the Bid and Ask prices via a web query?
Any assistance would be very much appreciated!
Tim
I'm trying to import all of the EUROSTOXX 50 spot prices from the following URL:
http://www.finanzen.net/aktien/realtimekurse.asp?inindex=11
What's happening is that the live updating Bid and Ask prices in the bottm table aren't displayed in the spreadsheet after running the web query. I assume this is probably because their properties are different to static data. I've tried all sorts of thing like changing properties in the web query etc, all to no avail. I've also tried the following script, but when it pastes to the spreadsheet, the code tends to fall over:
Code:
Sub Spot_Data_Import()
On Error GoTo Err
Application.ScreenUpdating = False
Dim Chosen_Date, Period, CopyRow, Bid, Offer, Spot As String
'This code imports the required Bid-Offer Data
Dim IE As Object
Sheets("Spot_Data").Select
Cells.Select
Selection.ClearContents ' erase previous data
ActiveSheet.Range("A1").Select
Set IE = CreateObject("InternetExplorer.Application")
With IE
.Visible = True
' should work for any URL
.navigate "http://www.finanzen.net/aktien/realtimekurse.asp?inindex=11"
Do Until .readyState = 4: DoEvents: Loop
'wait until the live streaming data appears
Application.Wait Now + TimeValue("00:00:05")
End With
IE.ExecWB 17, 0 '// SelectAll
IE.ExecWB 12, 2 '// Copy selection
ActiveSheet.Paste
ActiveSheet.Range("A1").Select
IE.Quit
MsgBox "Spot price updated!"
Exit Sub
Application.EnableEvents = True
Err:
Err.Clear
Select Case MsgBox("The following error has occured: " & vbCr & vbCr & _
"Either the website wasn't able to provide a figure," & vbCr & vbCr & _
"or the import format was incorrect." & vbCr & vbCr & _
"Do you want to try again?", _
vbYesNo, "Error")
Case vbYes: Call Spot_Data_Import
End Select
Exit Sub
End Sub
Can anyone help me improve the vba above or is anyone able to point me in the right direction for the vba to import the Bid and Ask prices via a web query?
Any assistance would be very much appreciated!
Tim