12 New Messages
Digest #2624
3a
RCHGetTableCell-Template-Yahoo-Real-Time-Quotes.xls Formula Questio by "tmallen2@bellsouth.net" tmallen2@bellsouth.net
3b
Re: RCHGetTableCell-Template-Yahoo-Real-Time-Quotes.xls Formula Ques by "Randy Harmelink" rharmelink
3c
Re: RCHGetTableCell-Template-Yahoo-Real-Time-Quotes.xls Formula Ques by "tmallen2@bellsouth.net" tmallen2@bellsouth.net
Messages
Mon May 20, 2013 8:01 am (PDT) . Posted by:
"Jacob Jose" pepecan47
A couple of questions:
- In the documentation, we have
"reportType" can be "is", "bs", or "cf" (which statement you want).
What is the reportType for Key Ratios?
- I have this formula, it works:
=smfGetCSVFile("http://financials.morningstar.com/ajax/exportKR2CSV.html?t= "&B1)
and this, it also works:
=smfGetCSVFile("http://financials.morningstar.com/ajax/exportKR2CSV.html?t=MCD&order=desc&dataType=R&period=12&columnYear=10&rounding=3&denominatorView=raw ")
This is probably a stupid question, I cannot make the second formula works if I want to have &B1 instead of the hard coded symbol MCD.
Regards,
Jose L. Jacob
- In the documentation, we have
"reportType&qu
What is the reportType for Key Ratios?
- I have this formula, it works:
=smfGetCSVFile(
and this, it also works:
=smfGetCSVFile(
This is probably a stupid question, I cannot make the second formula works if I want to have &B1 instead of the hard coded symbol MCD.
Regards,
Jose L. Jacob
Mon May 20, 2013 9:40 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
I don't think there is a reportType for KeyRatios, It's within it's own
report.
Your second question is just a 3-string concatenation instead of a 2-string
concatenation:
=smfGetCSVFile("http://financials.morningstar.com/ajax/exportKR2CSV.html?t=
"&B1&"&order=desc&dataType=R&period=12&columnYear=10&rounding=3&denominatorView=raw")
Or you could just move the ticker parameter to the end:
=smfGetCSVFile("
http://financials.morningstar.com/ajax/exportKR2CSV.html?order=desc&dataType=R&period=12&columnYear=10&rounding=3&denominatorView=raw&t=
"&B1)
The parameters can be in any order.
On Mon, May 20, 2013 at 7:55 AM, Jacob Jose <pepecan47@yahoo.ca > wrote:
>
>
> A couple of questions:
>
> - In the documentation, we have
> "reportType" can be "is", "bs", or "cf" (which statement you want).
>
> What is the reportType for Key Ratios?
>
> - I have this formula, it works:
> =smfGetCSVFile("
> http://financials.morningstar.com/ajax/exportKR2CSV.html?t= "&B1)
>
> and this, it also works:
> =smfGetCSVFile("
> http://financials.morningstar.com/ajax/exportKR2CSV.html?t=MCD&order=desc&dataType=R&period=12&columnYear=10&rounding=3&denominatorView=raw
> ")
>
> This is probably a stupid question, I cannot make the second formula works
> if I want to have &B1 instead of the hard coded symbol MCD.
>
>
report.
Your second question is just a 3-string concatenation instead of a 2-string
concatenation:
=smfGetCSVFile(
"&B1&"
Or you could just move the ticker parameter to the end:
=smfGetCSVFile(
http://financials.
"&B1)
The parameters can be in any order.
On Mon, May 20, 2013 at 7:55 AM, Jacob Jose <pepecan47@yahoo.
>
>
> A couple of questions:
>
> - In the documentation, we have
> "reportType&qu
>
> What is the reportType for Key Ratios?
>
> - I have this formula, it works:
> =smfGetCSVFile(
> http://financials.
>
> and this, it also works:
> =smfGetCSVFile(
> http://financials.
> ")
>
> This is probably a stupid question, I cannot make the second formula works
> if I want to have &B1 instead of the hard coded symbol MCD.
>
>
Mon May 20, 2013 9:08 am (PDT) . Posted by:
"bob_15861586" bob_15861586
Randy,
Thanks for the response. I am a bit confused by your answer. Are you
saying that I cannot get the Delta using Excel functions?
I think your Excel functions are excellent and I think it
is great that you offer them to the public for no charge.
Bob
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> The month and day should be a single parameter, which is why you get the
> error message you do.
>
> However, it appears you can't get SPX quotes from OX either. because they
> also do special coding of their ticker symbols for the options.
>
> For example, typically something like "IWM" will have an option ticker
> symbol like "IWM^^^...". But, for SPX, it appear the weeklies are
> "SPXW^^..." and the quarterlies are "SPXQ^^^...". So the option prefixes
> aren't matching the web page ticker symbol.
>
> So, for example, this is what the add-in is using to try to get the data:
>
> =RCHGetTableCell("
> https://www.optionsxpress.com/OXNetTools/Chains/index.aspx?Range=0&lstMarket=0&ChainType=3&lstMonths=12/31/2013;3&sLabel&Symbol=SPX
> ",-5,"SPX^^^131231C01700000")
>
> ...but this is what is actually needed:
>
> =RCHGetTableCell("
> https://www.optionsxpress.com/OXNetTools/Chains/index.aspx?Range=0&lstMarket=0&ChainType=3&lstMonths=12/31/2013;3&sLabel&Symbol=SPX
> ",-5,"SPXQ^^131231C01700000")
>
> On Fri, May 17, 2013 at 1:33 PM, bob_15861586 <bob_15861586@...>wrote:
>
> > I am trying to get the delta of an option expiring on December 31 of this
> > year. As such, I used the following function call:
> > =smfGetOptionQuotes("SPX Dec 31 2013 $1700 Call","5",,"OX")
> > However, what I get is invalid put/call indicator. What am I doing wrong?
> >
>
Thanks for the response. I am a bit confused by your answer. Are you
saying that I cannot get the Delta using Excel functions?
I think your Excel functions are excellent and I think it
is great that you offer them to the public for no charge.
Bob
--- In smf_addin@yahoogrou
>
> The month and day should be a single parameter, which is why you get the
> error message you do.
>
> However, it appears you can't get SPX quotes from OX either. because they
> also do special coding of their ticker symbols for the options.
>
> For example, typically something like "IWM" will have an option ticker
> symbol like "IWM^^^
> "SPXW^^
> aren't matching the web page ticker symbol.
>
> So, for example, this is what the add-in is using to try to get the data:
>
> =RCHGetTableCell(
> https://www.
> ",-5,"
>
> ...but this is what is actually needed:
>
> =RCHGetTableCell(
> https://www.
> ",-5,"
>
> On Fri, May 17, 2013 at 1:33 PM, bob_15861586 <bob_15861586@
>
> > I am trying to get the delta of an option expiring on December 31 of this
> > year. As such, I used the following function call:
> > =smfGetOptionQuotes
> > However, what I get is invalid put/call indicator. What am I doing wrong?
> >
>
Mon May 20, 2013 9:50 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
I'm saying you can't get SPX index option quotes with the
smfGetOptionQuotes() function, but you can get them with your own
RCHGetTableCell() functions. For example:
=RCHGetTableCell("
https://www.optionsxpress.com/OXNetTools/Chains/index.aspx?Range=0&lstMarket=0&ChainType=3&lstMonths=12/31/2013;3&Symbol=SPX
",-5,"SPXQ^^131231C01700000")
On Mon, May 20, 2013 at 9:08 AM, bob_15861586 <bob_15861586@yahoo.com >wrote:
>
> Thanks for the response. I am a bit confused by your answer. Are you
> saying that I cannot get the Delta using Excel functions?
>
smfGetOptionQuotes(
RCHGetTableCell(
=RCHGetTableCell(
https://www.
",-5,"
On Mon, May 20, 2013 at 9:08 AM, bob_15861586 <bob_15861586@
>
> Thanks for the response. I am a bit confused by your answer. Are you
> saying that I cannot get the Delta using Excel functions?
>
Mon May 20, 2013 3:26 pm (PDT) . Posted by:
"tmallen2@bellsouth.net" tmallen2@bellsouth.net
I am trying to get the formula in cell C4 in the template below to work in my spreadsheet. The template is located here:
Files > Templates and Examples > RCHGetTableCell
RCHGetTableCell-Template-Yahoo-Real-Time-Quotes.xls
This is the formula in cell C4:
=RCHGetTableCell("http://finance.yahoo.com/q/ecn?s= "&$C$2&"&"&_NOW,1,"Last Trade:")
I only change $C$2 to $B$5
It should work but something is causing it the generate a #Value error
I want to use this formula to generate a Real Time Yahoo Quote if possible in my spreadsheet.
I even tried copying the entire template into a new blank workbook and it still will not work.
The downloaded template runs perfectly in real time.
I suspect it might have something to do with the _NOW section of the formula or a macro that is not loading.
Any ideas anyone?
Thanks.
Files > Templates and Examples > RCHGetTableCell
RCHGetTableCell-
This is the formula in cell C4:
=RCHGetTableCell(
I only change $C$2 to $B$5
It should work but something is causing it the generate a #Value error
I want to use this formula to generate a Real Time Yahoo Quote if possible in my spreadsheet.
I even tried copying the entire template into a new blank workbook and it still will not work.
The downloaded template runs perfectly in real time.
I suspect it might have something to do with the _NOW section of the formula or a macro that is not loading.
Any ideas anyone?
Thanks.
Mon May 20, 2013 3:39 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
As written, "_NOW" is a cell reference. You would get a #VALUE! error of
that isn't a defined name.
But you shouldn't even need it, since the macro uses smfForceRecalculation
to update the workbook (and all other open workbooks).
I just tried this in cell K9 of the template:
=smfGetTagContent("http://finance.yahoo.com/q/ecn?s=
"&$B9,"span",-1,"yfs_market_time")
...to pick up the market time from the real-time quotes page, and it
updated just fine.
On Mon, May 20, 2013 at 2:20 PM, tmallen2@bellsouth.net <
tmallen2@bellsouth.net > wrote:
> I am trying to get the formula in cell C4 in the template below to work in
> my spreadsheet. The template is located here:
>
> Files > Templates and Examples > RCHGetTableCell
>
> RCHGetTableCell-Template-Yahoo-Real-Time-Quotes.xls
>
> This is the formula in cell C4:
>
> =RCHGetTableCell("http://finance.yahoo.com/q/ecn?s= "&$C$2&"&"&_NOW,1,"Last
> Trade:")
>
> I only change $C$2 to $B$5
>
> It should work but something is causing it the generate a #Value error
>
> I want to use this formula to generate a Real Time Yahoo Quote if possible
> in my spreadsheet.
>
> I even tried copying the entire template into a new blank workbook and it
> still will not work.
>
> The downloaded template runs perfectly in real time.
>
> I suspect it might have something to do with the _NOW section of the
> formula or a macro that is not loading.
>
> Any ideas anyone?
>
that isn't a defined name.
But you shouldn'
to update the workbook (and all other open workbooks).
I just tried this in cell K9 of the template:
=smfGetTagContent(
"&$B9,"
...to pick up the market time from the real-time quotes page, and it
updated just fine.
On Mon, May 20, 2013 at 2:20 PM, tmallen2@bellsouth.
tmallen2@bellsouth.
> I am trying to get the formula in cell C4 in the template below to work in
> my spreadsheet. The template is located here:
>
> Files > Templates and Examples > RCHGetTableCell
>
> RCHGetTableCell-
>
> This is the formula in cell C4:
>
> =RCHGetTableCell(
> Trade:"
>
> I only change $C$2 to $B$5
>
> It should work but something is causing it the generate a #Value error
>
> I want to use this formula to generate a Real Time Yahoo Quote if possible
> in my spreadsheet.
>
> I even tried copying the entire template into a new blank workbook and it
> still will not work.
>
> The downloaded template runs perfectly in real time.
>
> I suspect it might have something to do with the _NOW section of the
> formula or a macro that is not loading.
>
> Any ideas anyone?
>
Mon May 20, 2013 8:39 pm (PDT) . Posted by:
"tmallen2@bellsouth.net" tmallen2@bellsouth.net
I did copy and paste the entire template into my spreadsheet after I wrote my question.
It said that I had a name conflict with Now() and did I want to rename it. I chose yes and that seemed to fix the problem. Once I had the template in my spreadsheet, it was easy to copy the formula where I needed it and change the ticker cell reference. I will see if it is providing real time data in the morning.
Thanks Randy for your work. I have tons more work to do rebuilding my financial spreadsheets but I like you functions much better than the old MSN one I was using that is obsolete and very limited in what it can do.
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> As written, "_NOW" is a cell reference. You would get a #VALUE! error of
> that isn't a defined name.
>
> But you shouldn't even need it, since the macro uses smfForceRecalculation
> to update the workbook (and all other open workbooks).
>
> I just tried this in cell K9 of the template:
>
> =smfGetTagContent("http://finance.yahoo.com/q/ecn?s=
> "&$B9,"span",-1,"yfs_market_time")
>
> ...to pick up the market time from the real-time quotes page, and it
> updated just fine.
>
> On Mon, May 20, 2013 at 2:20 PM, tmallen2@... <
> tmallen2@...> wrote:
>
> > I am trying to get the formula in cell C4 in the template below to work in
> > my spreadsheet. The template is located here:
> >
> > Files > Templates and Examples > RCHGetTableCell
> >
> > RCHGetTableCell-Template-Yahoo-Real-Time-Quotes.xls
> >
> > This is the formula in cell C4:
> >
> > =RCHGetTableCell("http://finance.yahoo.com/q/ecn?s= "&$C$2&"&"&_NOW,1,"Last
> > Trade:")
> >
> > I only change $C$2 to $B$5
> >
> > It should work but something is causing it the generate a #Value error
> >
> > I want to use this formula to generate a Real Time Yahoo Quote if possible
> > in my spreadsheet.
> >
> > I even tried copying the entire template into a new blank workbook and it
> > still will not work.
> >
> > The downloaded template runs perfectly in real time.
> >
> > I suspect it might have something to do with the _NOW section of the
> > formula or a macro that is not loading.
> >
> > Any ideas anyone?
> >
>
It said that I had a name conflict with Now() and did I want to rename it. I chose yes and that seemed to fix the problem. Once I had the template in my spreadsheet, it was easy to copy the formula where I needed it and change the ticker cell reference. I will see if it is providing real time data in the morning.
Thanks Randy for your work. I have tons more work to do rebuilding my financial spreadsheets but I like you functions much better than the old MSN one I was using that is obsolete and very limited in what it can do.
--- In smf_addin@yahoogrou
>
> As written, "_NOW" is a cell reference. You would get a #VALUE! error of
> that isn't a defined name.
>
> But you shouldn'
> to update the workbook (and all other open workbooks).
>
> I just tried this in cell K9 of the template:
>
> =smfGetTagContent(
> "&$B9,"
>
> ...to pick up the market time from the real-time quotes page, and it
> updated just fine.
>
> On Mon, May 20, 2013 at 2:20 PM, tmallen2@... <
> tmallen2@...
>
> > I am trying to get the formula in cell C4 in the template below to work in
> > my spreadsheet. The template is located here:
> >
> > Files > Templates and Examples > RCHGetTableCell
> >
> > RCHGetTableCell-
> >
> > This is the formula in cell C4:
> >
> > =RCHGetTableCell(
> > Trade:"
> >
> > I only change $C$2 to $B$5
> >
> > It should work but something is causing it the generate a #Value error
> >
> > I want to use this formula to generate a Real Time Yahoo Quote if possible
> > in my spreadsheet.
> >
> > I even tried copying the entire template into a new blank workbook and it
> > still will not work.
> >
> > The downloaded template runs perfectly in real time.
> >
> > I suspect it might have something to do with the _NOW section of the
> > formula or a macro that is not loading.
> >
> > Any ideas anyone?
> >
>
Mon May 20, 2013 8:21 pm (PDT) . Posted by:
"bobc94595" bobc94595
What will you do to your templates in order to get Zacks info? I can't get Zacks.com on Firefox, IE, and Chrome, and can't get any Excel download with a Zacks code. The spreadsheets work with all other RCHGetElementNumbers.
--- In smf_addin@yahoogroups.com , "V. Shankar" <tarakayan@...> wrote:
>
> ZACKS in their website (think comes under media room) have made changes in their reports. They now have so many with several options. All my templates that work with downloads have to be redone.
>
>
> Sent from Yahoo! Mail on my iPad
>
--- In smf_addin@yahoogrou
>
> ZACKS in their website (think comes under media room) have made changes in their reports. They now have so many with several options. All my templates that work with downloads have to be redone.
>
>
> Sent from Yahoo! Mail on my iPad
>
Mon May 20, 2013 8:43 pm (PDT) . Posted by:
"Roger" rogerdiazbernal
Hi Randy:
I need to get Next Earnings Date from Yahoo Finance. I am using element 1285 from function RCHGetElementNumber but I get the following:
Get Data
IRBT 07-22-13
LOPE N/A
SPWR N/A
IMOS N/A
GPRE N/A
WETF 07-25-13
However the data are displayed on the following web
web Data
IRBT 07-23-13
LOPE 08-05-13
SPWR 08-05-13
IMOS 08-12-13
GPRE N/A
WETF 07-26-13
Am I using the correct element?
Roger
I need to get Next Earnings Date from Yahoo Finance. I am using element 1285 from function RCHGetElementNumber but I get the following:
Get Data
IRBT 07-22-13
LOPE N/A
SPWR N/A
IMOS N/A
GPRE N/A
WETF 07-25-13
However the data are displayed on the following web
web Data
IRBT 07-23-13
LOPE 08-05-13
SPWR 08-05-13
IMOS 08-12-13
GPRE N/A
WETF 07-26-13
Am I using the correct element?
Roger
Mon May 20, 2013 9:33 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Yahoo posts the earnings date sporadically on the page it was originally
available on, when the element was created. They used to only post the
earnings date a week or two before earnings, but now are lax on that.
A workaround is to pick it up from their Quotes page:
=RCHGetTableCell("http://finance.yahoo.com/q?s=LOPE ",1,"Next Earnings
Date:")
I'm updating element 1285 to get it from there.
On Mon, May 20, 2013 at 8:43 PM, Roger <rogerdiazb@gmail.com > wrote:
> Hi Randy:
> I need to get Next Earnings Date from Yahoo Finance. I am using element
> 1285 from function RCHGetElementNumber but I get the following:
> Get Data
> IRBT 07-22-13
> LOPE N/A
> SPWR N/A
> IMOS N/A
> GPRE N/A
> WETF 07-25-13
>
> However the data are displayed on the following web
> web Data
> IRBT 07-23-13
> LOPE 08-05-13
> SPWR 08-05-13
> IMOS 08-12-13
> GPRE N/A
> WETF 07-26-13
>
> Am I using the correct element?
>
available on, when the element was created. They used to only post the
earnings date a week or two before earnings, but now are lax on that.
A workaround is to pick it up from their Quotes page:
=RCHGetTableCell(
Date:")
I'm updating element 1285 to get it from there.
On Mon, May 20, 2013 at 8:43 PM, Roger <rogerdiazb@gmail.
> Hi Randy:
> I need to get Next Earnings Date from Yahoo Finance. I am using element
> 1285 from function RCHGetElementNumber but I get the following:
> Get Data
> IRBT 07-22-13
> LOPE N/A
> SPWR N/A
> IMOS N/A
> GPRE N/A
> WETF 07-25-13
>
> However the data are displayed on the following web
> web Data
> IRBT 07-23-13
> LOPE 08-05-13
> SPWR 08-05-13
> IMOS 08-12-13
> GPRE N/A
> WETF 07-26-13
>
> Am I using the correct element?
>
Mon May 20, 2013 9:25 pm (PDT) . Posted by:
"sheydon" sheydon
Let me first start out by saying I've been using this SUPER helpful Add-in for several years now, I literally can't imagine life without it. Thank you!!
I've installed the latest version, I'm running Google Chrome under Excel 2007.
My recent problem is I have my spreadsheet looking up the 52 week high and low using a RCHGetYahooQuote table ("j" and "k" codes top row). This seems to work fine for some stocks, but lately other stocks the information is out of date.
I've checked the information on Yahoo directly and it's current - so it appears my setup is either not pulling in the current data, or it's pulling from a different source. Other potentially helpful information:
1) The current stock prices seem to be pulling in just fine - also part of the same table.
2) My Internet settings are setup to pull a fresh version of the webpage, not just from the cache
3) I have utilized the SMFForceCalculation macro - which updates other data like the current market price
All of this and some stocks just don't seem to be having their 52 week high / low pulled in.
Any thoughts?
Thanks in advance for your help and for this wonderful tool!!!
Scott
I've installed the latest version, I'm running Google Chrome under Excel 2007.
My recent problem is I have my spreadsheet looking up the 52 week high and low using a RCHGetYahooQuote table ("j" and "k" codes top row). This seems to work fine for some stocks, but lately other stocks the information is out of date.
I've checked the information on Yahoo directly and it's current - so it appears my setup is either not pulling in the current data, or it's pulling from a different source. Other potentially helpful information:
1) The current stock prices seem to be pulling in just fine - also part of the same table.
2) My Internet settings are setup to pull a fresh version of the webpage, not just from the cache
3) I have utilized the SMFForceCalculation macro - which updates other data like the current market price
All of this and some stocks just don't seem to be having their 52 week high / low pulled in.
Any thoughts?
Thanks in advance for your help and for this wonderful tool!!!
Scott
Mon May 20, 2013 9:48 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
When you say you've checked the information on Yahoo, do you mean you
looked at the web pages or the CSV file itself? The two can be different.
We've seen it in the past.
For example, a quick look at the CSV file for ticker MMM in FireFox would
be:
view-source:
http://download.finance.yahoo.com/d/quotes.csv?s=MMM&f=sl1jk&e=.csv
On Mon, May 20, 2013 at 8:50 PM, sheydon <
scott.heydon.tu97@alum.dartmouth.org > wrote:
>
> I've installed the latest version, I'm running Google Chrome under Excel
> 2007.
>
> My recent problem is I have my spreadsheet looking up the 52 week high and
> low using a RCHGetYahooQuote table ("j" and "k" codes top row). This seems
> to work fine for some stocks, but lately other stocks the information is
> out of date.
>
> I've checked the information on Yahoo directly and it's current - so it
> appears my setup is either not pulling in the current data, or it's pulling
> from a different source. Other potentially helpful information:
>
> 1) The current stock prices seem to be pulling in just fine - also part of
> the same table.
> 2) My Internet settings are setup to pull a fresh version of the webpage,
> not just from the cache
> 3) I have utilized the SMFForceCalculation macro - which updates other
> data like the current market price
>
> All of this and some stocks just don't seem to be having their 52 week
> high / low pulled in.
>
> Any thoughts?
>
looked at the web pages or the CSV file itself? The two can be different.
We've seen it in the past.
For example, a quick look at the CSV file for ticker MMM in FireFox would
be:
view-source:
http://download.
On Mon, May 20, 2013 at 8:50 PM, sheydon <
scott.heydon.
>
> I've installed the latest version, I'm running Google Chrome under Excel
> 2007.
>
> My recent problem is I have my spreadsheet looking up the 52 week high and
> low using a RCHGetYahooQuote table ("j" and "k" codes top row). This seems
> to work fine for some stocks, but lately other stocks the information is
> out of date.
>
> I've checked the information on Yahoo directly and it's current - so it
> appears my setup is either not pulling in the current data, or it's pulling
> from a different source. Other potentially helpful information:
>
> 1) The current stock prices seem to be pulling in just fine - also part of
> the same table.
> 2) My Internet settings are setup to pull a fresh version of the webpage,
> not just from the cache
> 3) I have utilized the SMFForceCalculation macro - which updates other
> data like the current market price
>
> All of this and some stocks just don't seem to be having their 52 week
> high / low pulled in.
>
> Any thoughts?
>
Tidak ada komentar:
Posting Komentar