15 New Messages
Digest #2517
Messages
Thu Feb 21, 2013 7:50 am (PST) . Posted by:
"alexcomp1" alexcomp1
Still early going for me but making progress. I can successfully use smfgetyahoooption But...
Seems it would be much easier to use their pre-defined tag as in "AXP130720C00075000" which they list for every option in their option chains. Is there a way?
Seems it would be much easier to use their pre-defined tag as in "AXP130720C000
Thu Feb 21, 2013 8:16 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Sure. Just use RCHGetTableCell() functions. For example, to get bid price,
either of:
=RCHGetTableCell("http://finance.yahoo.com/q?s=AXP130720C00075000 ",1,">Bid")
=RCHGetTableCell("http://finance.yahoo.com/q/op?s=AXP&m=2013-07
",3,"AXP130720C00075000")
The first would be slowest, since you would need one web page access per
option contract retrieval. The second would be faster, if you get a number
of options from a single month, since all contracts for that month are
available in one Internet access (this is what smfGetOptionQuotes() and
smfGetYahooOptionQuote() both do to actually retrieve the data).
Another option would be to use the new, as yet undocumented,
smfYahooAPIData() function:
=smfYahooAPIData("AXP", "Bid","f","AXP130720C00075000")
The advantage of this is that all of the option contracts are available
with a single Internet access.
The second parameter can be any of:
-- StrikePrice
-- LastPrice
-- Change
-- Bid
-- Ask
-- Vol
-- OpenInt
On Thu, Feb 21, 2013 at 8:50 AM, alexcomp1 sgg@alexcomp.com > wrote:
> Still early going for me but making progress. I can successfully use
> smfgetyahoooption But...
>
> Seems it would be much easier to use their pre-defined tag as in
> "AXP130720C00075000" which they list for every option in their option
> chains. Is there a way?
>
either of:
=RCHGetTableCell(
=RCHGetTableCell(
",3,"
The first would be slowest, since you would need one web page access per
option contract retrieval. The second would be faster, if you get a number
of options from a single month, since all contracts for that month are
available in one Internet access (this is what smfGetOptionQuotes(
smfGetYahooOptionQu
Another option would be to use the new, as yet undocumented,
smfYahooAPIData(
=smfYahooAPIData(
The advantage of this is that all of the option contracts are available
with a single Internet access.
The second parameter can be any of:
-- StrikePrice
-- LastPrice
-- Change
-- Bid
-- Ask
-- Vol
-- OpenInt
On Thu, Feb 21, 2013 at 8:50 AM, alexcomp1 sgg@alexcomp.
> Still early going for me but making progress. I can successfully use
> smfgetyahoooption But...
>
> Seems it would be much easier to use their pre-defined tag as in
> "AXP130720C000
> chains. Is there a way?
>
Thu Feb 21, 2013 9:23 am (PST) . Posted by:
"alexcomp1" alexcomp1
Do a lot of options. Able to get quotes using this:
=smfGetYahooOptionQuote("AXP","C",DATE(2013,7,1),62.5,"l")
But I don't know how to force racalculation. I learned how to recalculate a stock price:
=RCHGetYahooQuotes("AXP","l1",NOW()) but the NOW() argument is rejected by the "getYahooOptionQuote" function.
How can I force recalculation of all lookup cells in a sheet?
=smfGetYahooOptionQ
But I don't know how to force racalculation. I learned how to recalculate a stock price:
=RCHGetYahooQuotes(
How can I force recalculation of all lookup cells in a sheet?
Thu Feb 21, 2013 12:19 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
You need to use the smfForceRecalculation macro to update the option
quotes, since it gets data from web pages instead of using a CSV file.
You'll also need to make sure your Internet Options are set up to always
get a fresh copy of a web page when it is visited.
Write-ups on both of those are available from the LINKS area of the Yahoo
group.
Personally, I find it a lot easier to do the more self documenting:
=smfGetOptionQuotes("AXP 7/20 2013 $62.50 Call","l",,"Y")
...than do do:
=smfGetYahooOptionQuote("AXP","C",DATE(2013,7,20),62.5,"l")
Of, if you have the individual components in worksheet cells, this:
=smfGetYahooOptionQuote(B6,E6,C6,D6,F6)
...the ticker symbol for smfGetOptionQuotes() could be created with:
=TRIM(B6)&TEXT(C6," m/d yyyy ")&TEXT(D6,"$0.00 ")&E6
Note that other option functions, such as smfGetOptionExpiry() and
smfGetOptionStrikes() can automatically create the appropriate ticker
symbols for smfGetOptionQuotes(). For more info, see the option-related
item in the LINKS area of the Yahoo group.
On Thu, Feb 21, 2013 at 10:23 AM, alexcomp1 sgg@alexcomp.com > wrote:
> Do a lot of options. Able to get quotes using this:
> =smfGetYahooOptionQuote("AXP","C",DATE(2013,7,1),62.5,"l")
>
> But I don't know how to force racalculation. I learned how to recalculate
> a stock price:
>
> =RCHGetYahooQuotes("AXP","l1",NOW()) but the NOW() argument is rejected by
> the "getYahooOptionQuote" function.
>
> How can I force recalculation of all lookup cells in a sheet?
>
quotes, since it gets data from web pages instead of using a CSV file.
You'll also need to make sure your Internet Options are set up to always
get a fresh copy of a web page when it is visited.
Write-ups on both of those are available from the LINKS area of the Yahoo
group.
Personally, I find it a lot easier to do the more self documenting:
=smfGetOptionQuotes
...than do do:
=smfGetYahooOptionQ
Of, if you have the individual components in worksheet cells, this:
=smfGetYahooOptionQ
...the ticker symbol for smfGetOptionQuotes(
=TRIM(B6)&TEXT(
Note that other option functions, such as smfGetOptionExpiry(
smfGetOptionStrikes
symbols for smfGetOptionQuotes(
item in the LINKS area of the Yahoo group.
On Thu, Feb 21, 2013 at 10:23 AM, alexcomp1 sgg@alexcomp.
> Do a lot of options. Able to get quotes using this:
> =smfGetYahooOptionQ
>
> But I don't know how to force racalculation. I learned how to recalculate
> a stock price:
>
> =RCHGetYahooQuotes(
> the "getYahooOptio
>
> How can I force recalculation of all lookup cells in a sheet?
>
Thu Feb 21, 2013 11:45 am (PST) . Posted by:
"j14mako" j14mako
Hi,
I wanted to get the data for stocks trading on the Australian ASX. For example, I tried to get the 10th year "FCF/Net Income" for HVN.AX using the "ASX:HVN" ticker syntax that Morningstar uses:
RCHGetElementNumber("XASX:HVN", 14388)
It didn't work. I've tried to encode the ":" as "%3a". No difference.
I looked at the URL of Morningstar's webpage. It was:
http://financials.morningstar.com/ratios/r.html?t=HVN®ion=AUS&culture=en-us
So it required "region=AUS". And the table is loaded dynamically.
Is there anything I can do?
Thanks
--
John
I wanted to get the data for stocks trading on the Australian ASX. For example, I tried to get the 10th year "FCF/Net Income" for HVN.AX using the "ASX:HVN" ticker syntax that Morningstar uses:
RCHGetElementNumber
It didn't work. I've tried to encode the ":" as "%3a"
I looked at the URL of Morningstar&#
http://financials.
So it required "region=
Is there anything I can do?
Thanks
--
John
Thu Feb 21, 2013 12:07 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
As you noted, the problem is that the web page you cite is a dynamically
generated web page, so there is nothing within the source code of the web
page for the add-in to extract as data.
The web page the element number uses is an older version of the web page,
such as:
http://quicktake.morningstar.com/Stock/CashFlowRatios10.asp?Symbol=MMM
I don't see an equivalent page for your company, so they may not be
available for non-US companies.
However, one workaround would be to grab their CSV file in another
worksheet, and grab the elements you want from there:
=smfGetCSVFile("
http://financials.morningstar.com/ajax/exportKR2CSV.html?t=XASX:HVN ")
Note that that is the file available from the "Export" button on the web
page you cite. This process may be what the rest of us will have to do when
the older version of the web page above is finally obsoleted.
On Thu, Feb 21, 2013 at 12:38 PM, j14mako johnyu@yahoo.com.au > wrote:
>
> I wanted to get the data for stocks trading on the Australian ASX. For
> example, I tried to get the 10th year "FCF/Net Income" for HVN.AX using
> the "ASX:HVN" ticker syntax that Morningstar uses:
>
> RCHGetElementNumber("XASX:HVN", 14388)
>
> It didn't work. I've tried to encode the ":" as "%3a". No difference.
>
> I looked at the URL of Morningstar's webpage. It was:
>
>
> http://financials.morningstar.com/ratios/r.html?t=HVN®ion=AUS&culture=en-us
>
> So it required "region=AUS". And the table is loaded dynamically.
>
> Is there anything I can do?
>
generated web page, so there is nothing within the source code of the web
page for the add-in to extract as data.
The web page the element number uses is an older version of the web page,
such as:
http://quicktake.
I don't see an equivalent page for your company, so they may not be
available for non-US companies.
However, one workaround would be to grab their CSV file in another
worksheet, and grab the elements you want from there:
=smfGetCSVFile(
http://financials.
Note that that is the file available from the "Export" button on the web
page you cite. This process may be what the rest of us will have to do when
the older version of the web page above is finally obsoleted.
On Thu, Feb 21, 2013 at 12:38 PM, j14mako johnyu@yahoo.
>
> I wanted to get the data for stocks trading on the Australian ASX. For
> example, I tried to get the 10th year "FCF/Net Income" for HVN.AX using
> the "ASX:HVN" ticker syntax that Morningstar uses:
>
> RCHGetElementNumber
>
> It didn't work. I've tried to encode the ":" as "%3a"
>
> I looked at the URL of Morningstar&#
>
>
> http://financials.
>
> So it required "region=
>
> Is there anything I can do?
>
Thu Feb 21, 2013 2:04 pm (PST) . Posted by:
"Roger" rogerdiazbernal
Hi Randy:
I try to get data from past Ten years of Revenues and EPS.
I am using
1.e. 2003-12
=RCHGetTableCell("http://financials.morningstar.com/ratios/r.html?t= "&$A$45&"®ion=USA&culture=en-us",1,"Revenue USD Mil")
=RCHGetTableCell("http://financials.morningstar.com/ratios/r.html?t= "&$A$45&"®ion=USA&culture=en-us",1,"Earnings Per Share USD")
but responding "Error"
Iappreciate your help
Roger
I try to get data from past Ten years of Revenues and EPS.
I am using
1.e. 2003-12
=RCHGetTableCell(
=RCHGetTableCell(
but responding "Error"
Iappreciate your help
Roger
Thu Feb 21, 2013 5:41 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Unfortunately, that web page is dynamically generated, so there are no data
items within the source code of the web page for the add-in to extract.
There aren't even any HTML tables in the source code.
I've been suggesting people use this on a separate worksheet, and grab the
data from it:
=smfGetCSVFile("
http://financials.morningstar.com/ajax/exportKR2CSV.html?t=MMM ")
On Thu, Feb 21, 2013 at 3:04 PM, Roger rogerdiazb@gmail.com > wrote:
>
> I try to get data from past Ten years of Revenues and EPS.
> I am using
> 1.e. 2003-12
> =RCHGetTableCell("http://financials.morningstar.com/ratios/r.html?t= "&$A$45&"®ion=USA&culture=en-us",1,"Revenue USD
> Mil")
>
> =RCHGetTableCell("http://financials.morningstar.com/ratios/r.html?t= "&$A$45&"®ion=USA&culture=en-us",1,"Earnings
> Per Share USD")
>
> but responding "Error"
>
> Iappreciate your help
>
items within the source code of the web page for the add-in to extract.
There aren't even any HTML tables in the source code.
I've been suggesting people use this on a separate worksheet, and grab the
data from it:
=smfGetCSVFile(
http://financials.
On Thu, Feb 21, 2013 at 3:04 PM, Roger rogerdiazb@gmail.
>
> I try to get data from past Ten years of Revenues and EPS.
> I am using
> 1.e. 2003-12
> =RCHGetTableCell(
> Mil")
>
> =RCHGetTableCell(
> Per Share USD")
>
> but responding "Error"
>
> Iappreciate your help
>
Thu Feb 21, 2013 6:43 pm (PST) . Posted by:
"Roger Diaz" rogerdiazbernal
Randy:
but just get
Growth Profitability and Financial Ratios for 3M Co
and no data.
Roger
but just get
Growth Profitability and Financial Ratios for 3M Co
and no data.
Roger
Thu Feb 21, 2013 6:50 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
You need to array-enter it over a range...
=smfGetCSVFile("
http://financials.morningstar.com/ajax/exportKR2CSV.html?t=MMM ")
For MMM, it's 110 rows by 12 columns of data. I don't know if that is
standard for all companies or not.
On Thu, Feb 21, 2013 at 7:43 PM, Roger Diaz rogerdiazb@gmail.com > wrote:
> but just get
> Growth Profitability and Financial Ratios for 3M Co
> and no data.
>
=smfGetCSVFile(
http://financials.
For MMM, it's 110 rows by 12 columns of data. I don't know if that is
standard for all companies or not.
On Thu, Feb 21, 2013 at 7:43 PM, Roger Diaz rogerdiazb@gmail.
> but just get
> Growth Profitability and Financial Ratios for 3M Co
> and no data.
>
Thu Feb 21, 2013 7:32 pm (PST) . Posted by:
"Roger Diaz" rogerdiazbernal
All data downloaed OK
Thanks Randy, you are great.
Roger
Thanks Randy, you are great.
Roger
Thu Feb 21, 2013 2:07 pm (PST) . Posted by:
"Jim" buckleca
Hi Randy,
I came across the "smfGetTagContent-Quick-Webpage-Examination.xls" in "Files > Templates and Examples > smfGetTagContent" and found it to be very helpful. Many thanks for providing as it's the best thing since sliced bread!
I was wondering if there are any other Quick templates planned for use with some of the other functions - like RCHGetTableCell or others?
Thanks again for the great product.
I came across the "smfGetTagCont
I was wondering if there are any other Quick templates planned for use with some of the other functions - like RCHGetTableCell or others?
Thanks again for the great product.
Thu Feb 21, 2013 5:45 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Anything particular in mind?
I finally threw that template together because it was something I did "on
the fly" so many times, I decided I may as well make a template out of it
and save myself some time. I've used it quite often myself since I created
it. But, even creating it "on the fly" only took a few seconds (after
"practicing" so many times) -- there really aren't that many moving parts
to it.
On Thu, Feb 21, 2013 at 3:07 PM, Jim buckleca@yahoo.com > wrote:
>
> I came across the "smfGetTagContent-Quick-Webpage-Examination.xls" in
> "Files > Templates and Examples > smfGetTagContent" and found it to be very
> helpful. Many thanks for providing as it's the best thing since sliced
> bread!
>
> I was wondering if there are any other Quick templates planned for use
> with some of the other functions - like RCHGetTableCell or others?
>
I finally threw that template together because it was something I did "on
the fly" so many times, I decided I may as well make a template out of it
and save myself some time. I've used it quite often myself since I created
it. But, even creating it "on the fly" only took a few seconds (after
"practicing&qu
to it.
On Thu, Feb 21, 2013 at 3:07 PM, Jim buckleca@yahoo.
>
> I came across the "smfGetTagCont
> "Files > Templates and Examples > smfGetTagContent&qu
> helpful. Many thanks for providing as it's the best thing since sliced
> bread!
>
> I was wondering if there are any other Quick templates planned for use
> with some of the other functions - like RCHGetTableCell or others?
>
Thu Feb 21, 2013 7:42 pm (PST) . Posted by:
"hjreed@swbell.net" hjreed@swbell.net
I was pulling the volume data for the Dow Jones Composite Average (DJIA) and the S&P 500 (^GSPC) using RCHGetYahooHistory and noticed the daily volumes for each were identical.
I looked at http://finance.yahoo.com/q/hp?s=%5EDJA+Historical+Prices and http://finance.yahoo.com/q/hp?s=%5EGSPC+Historical+Prices.
Sure enough they are identical in Yahoo. Should they be?
I guess I figured the volumes should be different. Another Yahoo error or am I looking at this wrong thinking they should be different?
I looked at http://finance.
Sure enough they are identical in Yahoo. Should they be?
I guess I figured the volumes should be different. Another Yahoo error or am I looking at this wrong thinking they should be different?
Thu Feb 21, 2013 8:10 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
I'm as confused as you are. Probably a good question for Yahoo...
On Thu, Feb 21, 2013 at 8:42 PM, hjreed@swbell.net hjreed@swbell.net >wrote:
> I was pulling the volume data for the Dow Jones Composite Average (DJIA)
> and the S&P 500 (^GSPC) using RCHGetYahooHistory and noticed the daily
> volumes for each were identical.
>
> I looked at http://finance.yahoo.com/q/hp?s=%5EDJA+Historical+Prices and
> http://finance.yahoo.com/q/hp?s=%5EGSPC+Historical+Prices.
>
> Sure enough they are identical in Yahoo. Should they be?
>
> I guess I figured the volumes should be different. Another Yahoo error or
> am I looking at this wrong thinking they should be different?
>
On Thu, Feb 21, 2013 at 8:42 PM, hjreed@swbell.
> I was pulling the volume data for the Dow Jones Composite Average (DJIA)
> and the S&P 500 (^GSPC) using RCHGetYahooHistory and noticed the daily
> volumes for each were identical.
>
> I looked at http://finance.
> http://finance.
>
> Sure enough they are identical in Yahoo. Should they be?
>
> I guess I figured the volumes should be different. Another Yahoo error or
> am I looking at this wrong thinking they should be different?
>
Tidak ada komentar:
Posting Komentar