VBA Web Query to Yahoo Finance

N

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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
Welcome to MrExcel!
Try with refresh on separate line:
.BackgroundQuery = True '(ERROR HERE)
.Refresh
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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!
 
Upvote 0
jaf146 said:
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!
Click to expand...

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
 
Upvote 0
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!
 
Upvote 0
martyloo said:
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!
Click to expand...
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
URL in cell used in VBA code to import web data
Replies
8
Views
1K
johnnyL
J
K
  • Question
Scraping a table from a website, VBA code needs to be updated, running into issues
Replies
1
Views
328
Dan_W
D
E
  • Question
Can anyone help me get my VBA code working? I have spent tons of time but can't quite fix it.
Replies
3
Views
2K
profray
P
S
  • Question
VBA help - yahoo finance downloader
Replies
7
Views
6K
sverrealbech
S
S
  • Question
Error when looping through Access queries "Too many client tasks"
2
Replies
15
Views
5K
ShaneGroff
S

Forum statistics

Threads
1,214,599
Messages
6,120,447
Members
448,966
Latest member
DannyC96

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top