Excel web queries using parameters

[Guest]

I am trying to setup a MS Excel web query based on the following URL:

http://finance.yahoo.com/currency/convert?amt=1&from=USD&to=JPY&submit=Convert

This is the Yahoo currency converter web page

When I introduce a parameter for amount this works OK as follows:

http://finance.yahoo.com/currency/convert?amt=["amount"]&from=USD&to=JPY&submit=Convert

However when I introduce parameters for the from and the to currency this
does not work as follows:

http://finance.yahoo.com/currency/convert?amt=1&from=["from"]&to=["to"]&submit=Convert

Can anyone tell me why? What am I doing wrong? Is there another way around
this?

The only thing I can possible think of is that the from and to currency on
the web site are drop down lists of values where the amount field is not.

Many thanks in anticipation

RobC

 

[Don Guillett]

Try this macro after you have imported using named ranges for amount, from &
to

Sub ConvertCurrency()
With Selection.QueryTable
.Connection = _
"URL;http://finance.yahoo.com/currency/convert?amt=" _
& [amount] & "&from=" & [from] & "&to=" & [to] & "&submit=Convert"
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "8,10,11,13"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

 

[Guest]

Hi Don

Many thanks for your reply

The problem is that I cannot import the named ranges for "from" & "to" when
I try I get a message on the web site in the Edit Web Query "Bad request" and
no data is imported into Excel

This is what I am using in the Address in Edit Web Query window then click
Inport

http://finance.yahoo.com/currency/convert?amt=1&from=["from"]&to=["to"]&submit=Convert

Regards

RobC

-----------------------------

Try this macro after you have imported using named ranges for amount, from &
to

Sub ConvertCurrency()
With Selection.QueryTable
.Connection = _
"URL;http://finance.yahoo.com/currency/convert?amt=" _
& [amount] & "&from=" & [from] & "&to=" & [to] & "&submit=Convert"
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "8,10,11,13"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)

I am trying to setup a MS Excel web query based on the following URL:

http://finance.yahoo.com/currency/convert?amt=1&from=USD&to=JPY&submit=Convert

This is the Yahoo currency converter web page

When I introduce a parameter for amount this works OK as follows:

http://finance.yahoo.com/currency/convert?amt=["amount"]&from=USD&to=JPY&submit=Convert

However when I introduce parameters for the from and the to currency this
does not work as follows:

http://finance.yahoo.com/currency/convert?amt=1&from=["from"]&to=["to"]&submit=Convert

Can anyone tell me why? What am I doing wrong? Is there another way around
this?

The only thing I can possible think of is that the from and to currency on
the web site are drop down lists of values where the amount field is not.

Many thanks in anticipation

RobC

 

[Don Guillett]

Send me your workbook and I will take a look.
--
Don Guillett
SalesAid Software
(e-mail address removed)

Hi Don

Many thanks for your reply

The problem is that I cannot import the named ranges for "from" & "to"
when
I try I get a message on the web site in the Edit Web Query "Bad request"
and
no data is imported into Excel

This is what I am using in the Address in Edit Web Query window then click
Inport

http://finance.yahoo.com/currency/convert?amt=1&from=["from"]&to=["to"]&submit=Convert

Regards

RobC

-----------------------------

Try this macro after you have imported using named ranges for amount,
from &
to

Sub ConvertCurrency()
With Selection.QueryTable
.Connection = _
"URL;http://finance.yahoo.com/currency/convert?amt=" _
& [amount] & "&from=" & [from] & "&to=" & [to] &
"&submit=Convert"
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "8,10,11,13"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)

I am trying to setup a MS Excel web query based on the following URL:

http://finance.yahoo.com/currency/convert?amt=1&from=USD&to=JPY&submit=Convert

This is the Yahoo currency converter web page

When I introduce a parameter for amount this works OK as follows:

http://finance.yahoo.com/currency/convert?amt=["amount"]&from=USD&to=JPY&submit=Convert

However when I introduce parameters for the from and the to currency
this
does not work as follows:

http://finance.yahoo.com/currency/convert?amt=1&from=["from"]&to=["to"]&submit=Convert

Can anyone tell me why? What am I doing wrong? Is there another way
around
this?

The only thing I can possible think of is that the from and to currency
on
the web site are drop down lists of values where the amount field is
not.

Many thanks in anticipation

RobC

 

[Guest]

Don

Many thanks for the tip. I have managed to get around the problem with a
macro as follows:

Dim amount As String
Dim currency_from As String
Dim currency_to As String

amount = 1
currency_from = InputBox("Please enter currency from")
currency_to = InputBox("Please enter currency to")

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/currency/convert?amt=" & amount &
"&from=" & currency_from & "&to=" & currency_to & "&submit=Convert" _
, Destination:=Range("A1"))
.Name = "convert?amt=1=USD&to=JPY&submit=Convert_16"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "13"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

Note the syntax in the URL line.

It seems to work OK. I am new to this VBA stuff any improvements greatfully
received. What is the .New line about?

Once again many thanks for your time it is very much appreciated.

--
Rob Cherry

Send me your workbook and I will take a look.
--
Don Guillett
SalesAid Software
(e-mail address removed)

Hi Don

Many thanks for your reply

The problem is that I cannot import the named ranges for "from" & "to"
when
I try I get a message on the web site in the Edit Web Query "Bad request"
and
no data is imported into Excel

This is what I am using in the Address in Edit Web Query window then click
Inport

http://finance.yahoo.com/currency/convert?amt=1&from=["from"]&to=["to"]&submit=Convert

Regards

RobC

-----------------------------

Try this macro after you have imported using named ranges for amount,
from &
to

Sub ConvertCurrency()
With Selection.QueryTable
.Connection = _
"URL;http://finance.yahoo.com/currency/convert?amt=" _
& [amount] & "&from=" & [from] & "&to=" & [to] &
"&submit=Convert"
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "8,10,11,13"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)
I am trying to setup a MS Excel web query based on the following URL:

http://finance.yahoo.com/currency/convert?amt=1&from=USD&to=JPY&submit=Convert

This is the Yahoo currency converter web page

When I introduce a parameter for amount this works OK as follows:

http://finance.yahoo.com/currency/convert?amt=["amount"]&from=USD&to=JPY&submit=Convert

However when I introduce parameters for the from and the to currency
this
does not work as follows:

http://finance.yahoo.com/currency/convert?amt=1&from=["from"]&to=["to"]&submit=Convert

Can anyone tell me why? What am I doing wrong? Is there another way
around
this?

The only thing I can possible think of is that the from and to currency
on
the web site are drop down lists of values where the amount field is
not.

Many thanks in anticipation

RobC

 

[Don Guillett]

The way you are doing it will ultimately create a lot of bloat by adding
queries each time without removing. So, I suggest you use something like
this after the query is created. I just tested. Again, named ranges or you
can use the input box if desired.

Sub ConvertCurrency()
With Sheets("Sheet1").QueryTables(1)
.Connection = _
"URL;http://finance.yahoo.com/currency/convert?amt=" _
& [amount] & "&from=" & [from] & "&to=" & [to] & "&submit=Convert"
.WebTables = "13"
.Refresh BackgroundQuery:=False
End With
End Sub


--
Don Guillett
SalesAid Software
(e-mail address removed)

Don

Many thanks for the tip. I have managed to get around the problem with a
macro as follows:

Dim amount As String
Dim currency_from As String
Dim currency_to As String

amount = 1
currency_from = InputBox("Please enter currency from")
currency_to = InputBox("Please enter currency to")

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/currency/convert?amt=" & amount &
"&from=" & currency_from & "&to=" & currency_to & "&submit=Convert" _
, Destination:=Range("A1"))
.Name = "convert?amt=1=USD&to=JPY&submit=Convert_16"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "13"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

Note the syntax in the URL line.

It seems to work OK. I am new to this VBA stuff any improvements
greatfully
received. What is the .New line about?

Once again many thanks for your time it is very much appreciated.

--
Rob Cherry

Send me your workbook and I will take a look.
--
Don Guillett
SalesAid Software
(e-mail address removed)

Hi Don

Many thanks for your reply

The problem is that I cannot import the named ranges for "from" & "to"
when
I try I get a message on the web site in the Edit Web Query "Bad
request"
and
no data is imported into Excel

This is what I am using in the Address in Edit Web Query window then
click
Inport

http://finance.yahoo.com/currency/convert?amt=1&from=["from"]&to=["to"]&submit=Convert

Regards

RobC

-----------------------------

:

Try this macro after you have imported using named ranges for amount,
from &
to

Sub ConvertCurrency()
With Selection.QueryTable
.Connection = _
"URL;http://finance.yahoo.com/currency/convert?amt=" _
& [amount] & "&from=" & [from] & "&to=" & [to] &
"&submit=Convert"
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "8,10,11,13"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)
I am trying to setup a MS Excel web query based on the following URL:

http://finance.yahoo.com/currency/convert?amt=1&from=USD&to=JPY&submit=Convert

This is the Yahoo currency converter web page

When I introduce a parameter for amount this works OK as follows:

http://finance.yahoo.com/currency/convert?amt=["amount"]&from=USD&to=JPY&submit=Convert

However when I introduce parameters for the from and the to currency
this
does not work as follows:

http://finance.yahoo.com/currency/convert?amt=1&from=["from"]&to=["to"]&submit=Convert

Can anyone tell me why? What am I doing wrong? Is there another way
around
this?

The only thing I can possible think of is that the from and to
currency
on
the web site are drop down lists of values where the amount field is
not.

Many thanks in anticipation

RobC

 

[Randy Harmelink]

If you're interested, I have a free open-source add-in that can grab
the data for you. The add-in, documentation on its functions, and
sample templates can be found in the files area of this Yahoo group:

http://finance.groups.yahoo.com/group/smf_addin/

I think the best way to get the data you want would be to use the
Yahoo current quotes interface. For example, with my add-in, I could
use this formula to get the last price for USD into JPY:

=RCHGetYahooQuotes("USDJPY=X","l1")

....or, if you wanted to get last traded price, date of the last trade,
time of the last trade, bid and ask prices, you could array-enter this
version of that formula over a 1-row by 5-column range:

=RCHGetYahooQuotes("USDJPY=X","l1d1t1ba")

You could get multiple ticker symbols as well -- this could be array-
entered over 2 columns and 6 rows:

=RCHGetYahooQuotes("USDJPY=X,USDGBP=X","sl1d1t1ba")

Also, there is a template in the files area that can get historical
data from this web site:

http://fx.sauder.ubc.ca/