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
 

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.

drsarao

Well-known Member
Joined
Sep 9, 2009
Messages
1,143
Office Version
  1. 2007
Platform
  1. Windows
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,143
Office Version
  1. 2007
Platform
  1. Windows
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,143
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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

ADVERTISEMENT

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
99
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,143
Office Version
  1. 2007
Platform
  1. Windows
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,136,341
Messages
5,675,207
Members
419,553
Latest member
hanahass

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
Top