VBA Web Query to Yahoo Finance

niras

New Member
Joined
Mar 3, 2010
Messages
3
I am trying to run a VBA Web Query to Yahoo Finance to get monthly stock price data. The error I get is : 'Run Time Error 1004' Unable to open <site> Cannot connect to the internet server.
I would greatly appreciate any help. My complete code is below.

Sub RunQuery(StSymbol As String)
'Dim ConnectStr As String
ConnectStr = "URL; http://finance.yahoo.com/q/hp?<wbr>s=" & StSymbol & "&a=" & BMonth & "&b=2" & "&c=" & BYear & "&d=" _
& EMonth & "&e=2&f=" & EYear & "&g=m"

Debug.Print ConnectStr
With ActiveSheet.QueryTables.Add(<wbr>Connection:=ConnectStr, Destination:=Range("$A$1"))
.Name = "StockPrices_" & StSymbol
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "20"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=True '(ERROR HERE)
End With
End Sub
 

drsarao

Well-known Member
Joined
Sep 9, 2009
Messages
1,112
Welcome to MrExcel!
Try with refresh on separate line:
.BackgroundQuery = True '(ERROR HERE)
.Refresh
 
Last edited:

drsarao

Well-known Member
Joined
Sep 9, 2009
Messages
1,112
This works:

Sub RunQuery() 'StSymbol As String)
'Dim ConnectStr As String
BMonth = 4
BYear = 2009
EMonth = 3
EYear = 2010
StSymbol = "YHOO"
ConnectStr = "URL; http://finance.yahoo.com/q/hp?s=" & StSymbol & "&a=" & BMonth & "&b=2" & "&c=" & BYear & "&d=" _
& EMonth & "&e=2&f=" & EYear & "&g=m"
Debug.Print ConnectStr
With ActiveSheet.QueryTables.Add(Connection:=ConnectStr, Destination:=Range("$A$1"))
.Name = "StockPrices_" & StSymbol
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "20"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.BackgroundQuery = True '(ERROR HERE)
.Refresh
End With
End Sub
 

niras

New Member
Joined
Mar 3, 2010
Messages
3
Hello Drsarao,

Thank you for very much your prompt reply.

However, I am still having the same problems as earlier. I copied and pasted your code directly and it does not work for me in the existing code or in a blank new worksheet or seperate sub either. Excel is set up right now to allow all internet access.

I think there may be an issue with the quotation marks with trying to call the Connection string, but I cannot find an alternative to the present code.

Is there anything else you can suggest?

Please help! Thank you.
 

drsarao

Well-known Member
Joined
Sep 9, 2009
Messages
1,112
The code in Post #3 runs fine on my Excel 2003 returning a table with monthly averages for May 09 to Mar 10 period.
I am appending a pruned down version (deleting all non-essential/default/duplicate stuff).
Try it on a new blank sheet.
If it still doesn't work, then check the net connectivity.
Is there a firewall? Which is barring Excel.
Is the connection thru proxy server?
Try to manually run a query in Excel and see if there are any issues.


Code:
Sub RunQuery()
BMonth = 4
BYear = 2009
EMonth = 3
EYear = 2010
StSymbol = "YHOO"
ConnectStr = "URL; http://finance.yahoo.com/q/hp?s=" & StSymbol & "&a=" _
            & BMonth & "&b=2" & "&c=" & BYear & "&d=" _
            & EMonth & "&e=2&f=" & EYear & "&g=m"
With ActiveSheet.QueryTables.Add(Connection:=ConnectStr, Destination:=Range("$A$1"))
    .Name = "StockPrices_" & StSymbol
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SaveData = True
    .AdjustColumnWidth = True
    .WebSelectionType = xlSpecifiedTables
    .WebTables = "20"
    .WebFormatting = xlWebFormattingNone
    .WebSingleBlockTextImport = False
    .Refresh
End With
End Sub
 

niras

New Member
Joined
Mar 3, 2010
Messages
3
drsarao,

Thank you very much for your help. I eventually got the code working now. All the difference between a working and non-working query personally was the single space in the ConnectStr: "URL; http://...."

Deleting that seems to have made the difference in this case! Thank you very much for your help!
 

jaf146

New Member
Joined
Apr 22, 2010
Messages
1
I have a program that uses this to find historic prices for a stock and the S&P500. The program then calculates average prices, standard deviation, etc.

My problem is that the calculations execute at the same time as the program is gathering the stock data- so the calculations show up as #DIV/0! (the calculations are done before the stock data shows up in excel).

I have tried using the Wait function but that just pauses the whole program instead of the subs with the calculations in them.

Is there anyway I can delay the execution of one particular sub?

Thanks!
 

polkovnik

New Member
Joined
May 7, 2004
Messages
6
I have a program that uses this to find historic prices for a stock and the S&P500. The program then calculates average prices, standard deviation, etc.

My problem is that the calculations execute at the same time as the program is gathering the stock data- so the calculations show up as #DIV/0! (the calculations are done before the stock data shows up in excel).

I have tried using the Wait function but that just pauses the whole program instead of the subs with the calculations in them.

Is there anyway I can delay the execution of one particular sub?

Thanks!
Why not try delaying calculation until after the queries have been refreshed?

Dim CalcState
CalcState = Application.Calculation
Application.Calculation = False

'Refresh queries here

Application.Calculation = CalcState
 

martyloo

Board Regular
Joined
Sep 29, 2010
Messages
96
Hi guys,

Very new at this only just started looking at this - I am currently trying to do the same thing I opened a new spreadsheet and just copied the above code drsarao showed on Mar 5th, 2010, 12:51 AM into a Macro but it is coming up with the error

Run Time Error '1004':

Unable to Open
http://finance.yahoo.com/q/hp?s=YHOO&a=4&b=2&c=2009&d=3&e=
Cannot connect to the Internet Server

What does this mean? Do I have something wrong with the code? Is it because i'm using Excel 2010? Do I need to sign up with Yahoo? Are the dates too old? Do I need some data in the spreadsheet before I run the Macro?

Please Help!
 

drsarao

Well-known Member
Joined
Sep 9, 2009
Messages
1,112
Hi guys,

Very new at this only just started looking at this - I am currently trying to do the same thing I opened a new spreadsheet and just copied the above code drsarao showed on Mar 5th, 2010, 12:51 AM into a Macro but it is coming up with the error

Run Time Error '1004':

Unable to Open
http://finance.yahoo.com/q/hp?s=YHOO&a=4&b=2&c=2009&d=3&e=
Cannot connect to the Internet Server

What does this mean? Do I have something wrong with the code? Is it because i'm using Excel 2010? Do I need to sign up with Yahoo? Are the dates too old? Do I need some data in the spreadsheet before I run the Macro? (None of these I think)

Please Help!
When I tried to run the code, I got "Web Query returned no data" error.
This was because Yahoo changed tables numbering. Fixed by replacing .WebTables = "20" with .WebTables = "15".

I have appended the corrected code with few additional modifications. (Lessons learnt over last 2 years!)

However, your error seems different. First try this modified code. If it doesn't work and you get the same error:
Check the net & internet connectivity.
Is there a firewall? Which is barring Excel.
Is the connection thru proxy server?
Try to manually run a query in Excel and see if there are any issues.
Code:
Sub RunQuery()
BMonth = 4
BYear = 2009
EMonth = 3
EYear = 2010
StSymbol = "YHOO"
ConnectStr = "URL; http://finance.yahoo.com/q/hp?s=" & StSymbol & "&a=" _
            & BMonth & "&b=2" & "&c=" & BYear & "&d=" _
            & EMonth & "&e=2&f=" & EYear & "&g=m"
With ActiveSheet.QueryTables.Add(Connection:=ConnectStr, Destination:=Range("$A$1"))
    .Name = "StockPrices_" & StSymbol
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = False 'this ensures that the macro control returns only AFTER the query has finished
    .RefreshStyle = xlOverwriteCells  'overwrite is more relevant if you want to run the same query again and again
    .SaveData = True
    .AdjustColumnWidth = True
    .WebSelectionType = xlSpecifiedTables
    .WebTables = "15"
    .WebFormatting = xlWebFormattingNone
    .WebSingleBlockTextImport = False
    .Refresh
    .Delete  'deleting the query does NOT delete the imported data
End With
End Sub
 

Forum statistics

Threads
1,081,693
Messages
5,360,668
Members
400,592
Latest member
katekoz

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top