15 New Messages
Digest #2930
Messages
Wed Jan 15, 2014 9:05 am (PST) . Posted by:
michalemory
I apologize if this has been asked before, but I couldn't find a similar question.
I'm using the RCHGETYAHOOHISTORY function to bring in a series of prices from a stock and the S&P 500. I'm wanting the end of the month closing price and it is bringing it in correctly, but it shows the first day of the month as the date. For instance, it will be in 12/31 closing price but show 12/1 (or whatever was the first trading day of the month).
What am I doing wrong and how can I get it to show the end of the month date? I know how to use the EOMONTH function but I'm wanting figure out what I'm doing wrong with this function.
Thanks in advance.
I'm using the RCHGETYAHOOHISTORY function to bring in a series of prices from a stock and the S&P 500. I'm wanting the end of the month closing price and it is bringing it in correctly, but it shows the first day of the month as the date. For instance, it will be in 12/31 closing price but show 12/1 (or whatever was the first trading day of the month).
What am I doing wrong and how can I get it to show the end of the month date? I know how to use the EOMONTH function but I'm wanting figure out what I'm doing wrong with this function.
Thanks in advance.
Wed Jan 15, 2014 9:19 am (PST) . Posted by:
"Randy Harmelink" rharmelink
That's just the way Yahoo returns the data. The date is the start of the
period, whether it's daily, weekly, or monthly.
Alternatively, you could use smfPricesByDates() to bring in a series of
prices. Then you can control the date of every closing price.
On Wed, Jan 15, 2014 at 10:05 AM, <michalemory@yahoo.com> wrote:
>
> I apologize if this has been asked before, but I couldn't find a similar
> question.
>
>
> I'm using the RCHGETYAHOOHISTORY function to bring in a series of prices
> from a stock and the S&P 500. I'm wanting the end of the month closing
> price and it is bringing it in correctly, but it shows the first day of the
> month as the date. For instance, it will be in 12/31 closing price but
> show 12/1 (or whatever was the first trading day of the month).
>
> What am I doing wrong and how can I get it to show the end of the month
> date? I know how to use the EOMONTH function but I'm wanting figure out
> what I'm doing wrong with this function.
>
>
period, whether it's daily, weekly, or monthly.
Alternatively, you could use smfPricesByDates(
prices. Then you can control the date of every closing price.
On Wed, Jan 15, 2014 at 10:05 AM, <michalemory@
>
> I apologize if this has been asked before, but I couldn't find a similar
> question.
>
>
> I'm using the RCHGETYAHOOHISTORY function to bring in a series of prices
> from a stock and the S&P 500. I'm wanting the end of the month closing
> price and it is bringing it in correctly, but it shows the first day of the
> month as the date. For instance, it will be in 12/31 closing price but
> show 12/1 (or whatever was the first trading day of the month).
>
> What am I doing wrong and how can I get it to show the end of the month
> date? I know how to use the EOMONTH function but I'm wanting figure out
> what I'm doing wrong with this function.
>
>
Wed Jan 15, 2014 10:32 am (PST) . Posted by:
michalemory
Ok, thanks!
Wed Jan 15, 2014 10:52 am (PST) . Posted by:
"Evin Wrighton" ejw9785
What other information can I give you that will help? I took over this task a few months ago, I've only come across the mis-calculation one or two times, and then when I re-ran for that particular date it was fine again.
SMF-Template-Misc-Technical-Indicators.xls - http://finance.groups.yahoo.com/group/smf_addin/files/Templates%20and%20Examples/SMF-Template-Misc-Technical-Indicators.xls
This is one of the links I have in the excel file as 93-2003 worksheet, and then there is a separate file that has the same file name, but is a Microsoft Office Excel Add-In (.xla)
- I checked the properties for these files, and both locations are the same.
I'm not sure if any of this helps, I'm pretty good with excel, but haven't had to resolve an issue before regarding a macro-enabled worksheet.
Thank you,
E
On Wednesday, January 15, 2014 11:18 AM, "smf_addin@yahoogroups.com" <smf_addin@yahoogroups.com> wrote:
EXCEL Stock Market Functions Add-in
EXCEL Stock Market Functions Add-in Group
15 New Messages
Digest #2929
1a
Re: Windows 8.1 Add In Location for Skydrive by "ridgebacksexcel" ridgebacksexcel
1b
Re: Windows 8.1 Add In Location for Skydrive by "Randy Harmelink" rharmelink
2a
Get Yahoo quotes by smortonm
2b
Re: Get Yahoo quotes by cats1017
2c
Re: Get Yahoo quotes by hamishthedenizen
2d
Re: Get Yahoo quotes by smortonm
2e
Re: Get Yahoo quotes by "Randy Harmelink" rharmelink
2f
Re: Get Yahoo quotes by rogacox
2g
Re: Get Yahoo quotes by rogacox
2h
Re: Get Yahoo quotes by mikemcq802
2i
Re: Get Yahoo quotes by "Randy Harmelink" rharmelink
2j
Re: Get Yahoo quotes by "Randy Harmelink" rharmelink
2k
Re: Get Yahoo quotes by mrothaus
3a
Excel calculating incorrectly by "Evin Wrighton" ejw9785
3b
Re: Excel calculating incorrectly by "Randy Harmelink" rharmelink
Messages
1a
Re: Windows 8.1 Add In Location for Skydrive
Wed Jan 15, 2014 3:24 am (PST) . Posted by:
"ridgebacksexcel" ridgebacksexcel
>>> Can you just use the smfFixLinks macro?<< <
What is this? I am not familiar with it.
The only work around I have found is to first "Save as" the file to My Documents folder on my computer then "Save" which puts a copy in my Skydrive folder on my computer then uploads a copy to the Skydrive cloud. Not ideal but it stops the links from breaking.
I am familiar with going to the Data tab, then edit links. I then browse to Program Files/SMF Add In and select the RCH_Stock_Market_ Function file which restores the link. That's a pain to do every time the file opens without the work around.
--- In smf_addin@yahoogrou ps.com, Randy Harmelink <rharmelink@ ...> wrote:
>
> I think the problem is that EXCEL can't resolve links to a network drive.
>
> Can you just use the smfFixLinks macro?
>
> On Tue, Jan 14, 2014 at 8:04 PM, ridgebacksexcel <tmallen2@ ...>wrote:
>
> > I have updated my computer to Windows 8.1 and have all my spreadsheets
> > located in my Skydrive folder.
> >
> > Every time I open an Excel spreadsheet that wants to use the SMF add in, I
> > get a location error for the add in as it cannot locate it on Skydrive.
> >
> > I go to edit links and change the source to Program files/SMF Add in
> > folder on my C: drive where I keep the add in. I save the Excel file, it
> > uploads to Skydrive.
> >
> > I then open it again from my skydrive folder and get the same error even
> > though I edited the link location.
> >
> > Has anyone else figured out how to get this to work without editing the
> > link every time you open an Excel file with the Add In?
> >
>
Reply to sender . Reply to group . Reply via Web Post . All Messages (4) . Top ^
1b
Re: Windows 8.1 Add In Location for Skydrive
Wed Jan 15, 2014 6:48 am (PST) . Posted by:
"Randy Harmelink" rharmelink
There is an smfFixLinks macro in the add-in. I have it assigned to a button
on my EXCEL toolbar, so I can run it any time I open a workbook with
location errors.
It just does a "Find and Replace all" on the hard-coded location that EXCEL
leaves on unresolved links.
See the entry in the LINKS area of the group for some information on
location errors and unresolved links.
On Wed, Jan 15, 2014 at 4:24 AM, ridgebacksexcel <tmallen2@bellsouth. net>wrote:
> >>> Can you just use the smfFixLinks macro?<< <
>
> What is this? I am not familiar with it.
>
Reply to sender . Reply to group . Reply via Web Post . All Messages (4) . Top ^
2a
Get Yahoo quotes
Wed Jan 15, 2014 4:41 am (PST) . Posted by:
smortonm
My ability to bring in Yahoo stock quotes is getting worse by the day. My spread sheet has about 100+ quotes and many fail to come in. It appears that MSN last quote is a day old? Is there another source of reliable last price quotes that is available. Any help greatly appreciated.
Sid
Reply to sender . Reply to group . Reply via Web Post . All Messages (11) . Top ^
2b
Re: Get Yahoo quotes
Wed Jan 15, 2014 6:19 am (PST) . Posted by:
cats1017
My spreadsheet is having the same problems - I have tried cutting down on the number of queries it pulls from Yahoo, but even a file with just a few queries takes a very long time and most of the quotes do not populate.
Reply to sender . Reply to group . Reply via Web Post . All Messages (11) . Top ^
2c
Re: Get Yahoo quotes
Wed Jan 15, 2014 6:33 am (PST) . Posted by:
hamishthedenizen
You can try to get them from Google using
=smfConvertData( smfGetTagContent ("https://www. google.com/ finance?q="&A1," span" ,-1," _l"" >" ))
Where cell A1 contains the ticker
Reply to sender . Reply to group . Reply via Web Post . All Messages (11) . Top ^
2d
Re: Get Yahoo quotes
Wed Jan 15, 2014 6:48 am (PST) . Posted by:
smortonm
Thank U very much -- this seems to be a lot better
Sid
Reply to sender . Reply to group . Reply via Web Post . All Messages (11) . Top ^
2e
Re: Get Yahoo quotes
Wed Jan 15, 2014 6:50 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Instead of doing the 100+ requests and having a few fail, why not just do
one array-entered formula and get all the data in one request? It should be
faster and more reliable.
On Wed, Jan 15, 2014 at 5:41 AM, <smmarder@yahoo. com> wrote:
>
> My ability to bring in Yahoo stock quotes is getting worse by the day. My
> spread sheet has about 100+ quotes and many fail to come in. It appears
> that MSN last quote is a day old? Is there another source of reliable last
> price quotes that is available. Any help greatly appreciated.
> Sid
>
Reply to sender . Reply to group . Reply via Web Post . All Messages (11) . Top ^
2f
Re: Get Yahoo quotes
Wed Jan 15, 2014 7:11 am (PST) . Posted by:
rogacox
Randy,
Do you have an example Excel file that uses an array to get information from Yahoo that does not return errors. In any spreadsheet tab I am only getting about a dozen rows of data and I have separated the other material after blank rows to separate tabs in the spreadsheet. I am having some of the same trouble reported in other comments, but in addition to quotes I use (from the element tables) 943, 629, 989, 990, and 986 and the quote function with arguments "r" for P/E, , and "t8" for target price. The "Error" returns seem almost random. I would appreciate a way around this. I could get quotes from another source but that does not solve the issue of the other data. Thanks in advance for your help.
Reply to sender . Reply to group . Reply via Web Post . All Messages (11) . Top ^
2g
Re: Get Yahoo quotes
Wed Jan 15, 2014 7:13 am (PST) . Posted by:
rogacox
An example for those of us that are not programmers would be appreciated. Thanks.
Reply to sender . Reply to group . Reply via Web Post . All Messages (11) . Top ^
2h
Re: Get Yahoo quotes
Wed Jan 15, 2014 7:25 am (PST) . Posted by:
mikemcq802
Go to the Files section of the group and look at the SMF-Template- RCHGetYahooQuote s example xls.
It's in the RCHGetYahooQuotes folder in the Files section, Templates and Examples.
I have also had some problems getting yahoo quotes the past week or so. My experience is my array of returned data is either all-there or all-missing. I use the SMFForceRecalculati on to refresh the data when that happens and it almost always returns the full set of data (see the Group's Links section for an explanation) . Yahoo is clearly having issues. The best thing to do is minimize the number of requests you make by using array formulas.
Reply to sender . Reply to group . Reply via Web Post . All Messages (11) . Top ^
2i
Re: Get Yahoo quotes
Wed Jan 15, 2014 7:29 am (PST) . Posted by:
"Randy Harmelink" rharmelink
The RCHGetElementNumber () function can't be array-entered. I thought you
were talking about getting quotes, with RCHGetYahooQuotes( ).
Yahoo's not the only service with such issues. MorningStar was down for a
bit yesterday morning as well.
There is a step-by-step example of setting up an RCHGetYahooQuotes( ) table.
It's pointed to both from the LINKS area of the group *and* the
documentation for RCHGetYahooQuotes( ).
On Wed, Jan 15, 2014 at 8:11 AM, <rogacox@yahoo. com> wrote:
>
> Do you have an example Excel file that uses an array to get information
> from Yahoo that does not return errors. In any spreadsheet tab I am only
> getting about a dozen rows of data and I have separated the other material
> after blank rows to separate tabs in the spreadsheet. I am having some of
> the same trouble reported in other comments, but in addition to quotes I
> use (from the element tables) 943, 629, 989, 990, and 986 and the quote
> function with arguments "r" for P/E, , and "t8" for target price. The
> "Error" returns seem almost random. I would appreciate a way around this.
> I could get quotes from another source but that does not solve the issue of
> the other data. Thanks in advance for your help.
>
Reply to sender . Reply to group . Reply via Web Post . All Messages (11) . Top ^
2j
Re: Get Yahoo quotes
Wed Jan 15, 2014 7:33 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Exactly. Just as an example, suppose there's a 1% chance of having an
error. If you get 100 pieces of data in an array-entered formula, you have
a 1% chance of failure (of everything). If you get those 100 pieces of data
with individual formulas, you have 63% chance of at least one item failing.
And with smfForceRecalculati on, it's easy to recover from the 1% error. But
if you recalculate with the 100 pieces of data, you once again have a 63%
chance of at least one item failing.
On Wed, Jan 15, 2014 at 8:25 AM, <mikemcq802@yahoo. com> wrote:
> The best thing to do is minimize the number of requests you make by
> using array formulas.
>
Reply to sender . Reply to group . Reply via Web Post . All Messages (11) . Top ^
2k
Re: Get Yahoo quotes
Wed Jan 15, 2014 8:18 am (PST) . Posted by:
mrothaus
Wonderful! I was searching for a way to use some source other than Yahoo.
For users that have preferred stocks that they look up, be forewarned that Yahoo uses a different ticker symbol than Google. The basic difference is that Yahoo has a "P" in it. For example, AHT series D preferred symbol:
Yahoo: AHT-PD
Google: AHT-D
Reply to sender . Reply to group . Reply via Web Post . All Messages (11) . Top ^
3a
Excel calculating incorrectly
Wed Jan 15, 2014 7:47 am (PST) . Posted by:
"Evin Wrighton" ejw9785
Hello,
I have a spreadsheet that is supposed to auto-calculate several things daily after I enter in the date, one of them is the S&P 500. I'm not sure what went wrong, but it calculated (.57%) on both 1/7 and 1/8 - when it should have been .61% on 1/7 and .02% on 1/8/14.
I noticed on one of your past messages that there was an update on the add-in? Could this be why my spreadsheet has stopped calculating correctly?
I have excel 2007 - I'm not sure what other information you need? This is something new I took over and wasn't expecting to have any issues with the calculating from the RCH_Stock_ Market_Functions excel add-in.
Thank you for your help!!
E
Reply to sender . Reply to group . Reply via Web Post . All Messages (2) . Top ^
3b
Re: Excel calculating incorrectly
Wed Jan 15, 2014 7:51 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Without knowing the details, I can only speculate. Two possibilities come
to mind immediately:
1. You may have your Internet Options set to not get a fresh copy of a web
page every time the site is visited.
2. Since you took it over from someone else, you may have location errors
-- EXCEL not resolving all the links properly because the add-in is in a
different location that the person that saved the workbook.
Both of these issues have entries in the LINKS area of the group.
On Wed, Jan 15, 2014 at 8:41 AM, Evin Wrighton <ejw9785@yahoo. com> wrote:
>
> I have a spreadsheet that is supposed to auto-calculate several things
> daily after I enter in the date, one of them is the S&P 500. I'm not sure
> what went wrong, but it calculated (.57%) on both 1/7 and 1/8 - when it
> should have been .61% on 1/7 and .02% on 1/8/14.
>
> I noticed on one of your past messages that there was an update on the
> add-in? Could this be why my spreadsheet has stopped calculating correctly?
>
> I have excel 2007 - I'm not sure what other information you need? This is
> something new I took over and wasn't expecting to have any issues with the
> calculating from the RCH_Stock_Market_ Functions excel add-in.
>
> Thank you for your help!!
>
> E
>
Reply to sender . Reply to group . Reply via Web Post . All Messages (2) . Top ^
Visit Your Group
>
View All Topics
>
Create New Topic
>
38 New Members
>
We are making changes based on your feedback, Thank you !
Submit Feedback
>
The Yahoo! Groups Product Blog
Check it out!
>
CHANGE SETTINGS
>
TERMS OF USE
>
UNSUBSCRIBE
>
SMF-Template-
This is one of the links I have in the excel file as 93-2003 worksheet, and then there is a separate file that has the same file name, but is a Microsoft Office Excel Add-In (.xla)
- I checked the properties for these files, and both locations are the same.
I'm not sure if any of this helps, I'm pretty good with excel, but haven't had to resolve an issue before regarding a macro-enabled worksheet.
Thank you,
E
On Wednesday, January 15, 2014 11:18 AM, "smf_addin@yahoogrou
EXCEL Stock Market Functions Add-in
EXCEL Stock Market Functions Add-in Group
15 New Messages
Digest #2929
1a
Re: Windows 8.1 Add In Location for Skydrive by "ridgebacksexc
1b
Re: Windows 8.1 Add In Location for Skydrive by "Randy Harmelink" rharmelink
2a
Get Yahoo quotes by smortonm
2b
Re: Get Yahoo quotes by cats1017
2c
Re: Get Yahoo quotes by hamishthedenizen
2d
Re: Get Yahoo quotes by smortonm
2e
Re: Get Yahoo quotes by "Randy Harmelink" rharmelink
2f
Re: Get Yahoo quotes by rogacox
2g
Re: Get Yahoo quotes by rogacox
2h
Re: Get Yahoo quotes by mikemcq802
2i
Re: Get Yahoo quotes by "Randy Harmelink" rharmelink
2j
Re: Get Yahoo quotes by "Randy Harmelink" rharmelink
2k
Re: Get Yahoo quotes by mrothaus
3a
Excel calculating incorrectly by "Evin Wrighton" ejw9785
3b
Re: Excel calculating incorrectly by "Randy Harmelink" rharmelink
Messages
1a
Re: Windows 8.1 Add In Location for Skydrive
Wed Jan 15, 2014 3:24 am (PST) . Posted by:
"ridgebacksexc
>>> Can you just use the smfFixLinks macro?<< <
What is this? I am not familiar with it.
The only work around I have found is to first "Save as" the file to My Documents folder on my computer then "Save" which puts a copy in my Skydrive folder on my computer then uploads a copy to the Skydrive cloud. Not ideal but it stops the links from breaking.
I am familiar with going to the Data tab, then edit links. I then browse to Program Files/SMF Add In and select the RCH_Stock_Market_ Function file which restores the link. That's a pain to do every time the file opens without the work around.
--- In smf_addin@yahoogrou ps.com, Randy Harmelink <rharmelink@ ...> wrote:
>
> I think the problem is that EXCEL can't resolve links to a network drive.
>
> Can you just use the smfFixLinks macro?
>
> On Tue, Jan 14, 2014 at 8:04 PM, ridgebacksexcel <tmallen2@ ...>wrote:
>
> > I have updated my computer to Windows 8.1 and have all my spreadsheets
> > located in my Skydrive folder.
> >
> > Every time I open an Excel spreadsheet that wants to use the SMF add in, I
> > get a location error for the add in as it cannot locate it on Skydrive.
> >
> > I go to edit links and change the source to Program files/SMF Add in
> > folder on my C: drive where I keep the add in. I save the Excel file, it
> > uploads to Skydrive.
> >
> > I then open it again from my skydrive folder and get the same error even
> > though I edited the link location.
> >
> > Has anyone else figured out how to get this to work without editing the
> > link every time you open an Excel file with the Add In?
> >
>
Reply to sender . Reply to group . Reply via Web Post . All Messages (4) . Top ^
1b
Re: Windows 8.1 Add In Location for Skydrive
Wed Jan 15, 2014 6:48 am (PST) . Posted by:
"Randy Harmelink" rharmelink
There is an smfFixLinks macro in the add-in. I have it assigned to a button
on my EXCEL toolbar, so I can run it any time I open a workbook with
location errors.
It just does a "Find and Replace all" on the hard-coded location that EXCEL
leaves on unresolved links.
See the entry in the LINKS area of the group for some information on
location errors and unresolved links.
On Wed, Jan 15, 2014 at 4:24 AM, ridgebacksexcel <tmallen2@
> >>> Can you just use the smfFixLinks macro?<< <
>
> What is this? I am not familiar with it.
>
Reply to sender . Reply to group . Reply via Web Post . All Messages (4) . Top ^
2a
Get Yahoo quotes
Wed Jan 15, 2014 4:41 am (PST) . Posted by:
smortonm
My ability to bring in Yahoo stock quotes is getting worse by the day. My spread sheet has about 100+ quotes and many fail to come in. It appears that MSN last quote is a day old? Is there another source of reliable last price quotes that is available. Any help greatly appreciated.
Sid
Reply to sender . Reply to group . Reply via Web Post . All Messages (11) . Top ^
2b
Re: Get Yahoo quotes
Wed Jan 15, 2014 6:19 am (PST) . Posted by:
cats1017
My spreadsheet is having the same problems - I have tried cutting down on the number of queries it pulls from Yahoo, but even a file with just a few queries takes a very long time and most of the quotes do not populate.
Reply to sender . Reply to group . Reply via Web Post . All Messages (11) . Top ^
2c
Re: Get Yahoo quotes
Wed Jan 15, 2014 6:33 am (PST) . Posted by:
hamishthedenizen
You can try to get them from Google using
=smfConvertData( smfGetTagContent ("https://www. google.com/ finance?q="
Where cell A1 contains the ticker
Reply to sender . Reply to group . Reply via Web Post . All Messages (11) . Top ^
2d
Re: Get Yahoo quotes
Wed Jan 15, 2014 6:48 am (PST) . Posted by:
smortonm
Thank U very much -- this seems to be a lot better
Sid
Reply to sender . Reply to group . Reply via Web Post . All Messages (11) . Top ^
2e
Re: Get Yahoo quotes
Wed Jan 15, 2014 6:50 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Instead of doing the 100+ requests and having a few fail, why not just do
one array-entered formula and get all the data in one request? It should be
faster and more reliable.
On Wed, Jan 15, 2014 at 5:41 AM, <smmarder@
>
> My ability to bring in Yahoo stock quotes is getting worse by the day. My
> spread sheet has about 100+ quotes and many fail to come in. It appears
> that MSN last quote is a day old? Is there another source of reliable last
> price quotes that is available. Any help greatly appreciated.
> Sid
>
Reply to sender . Reply to group . Reply via Web Post . All Messages (11) . Top ^
2f
Re: Get Yahoo quotes
Wed Jan 15, 2014 7:11 am (PST) . Posted by:
rogacox
Randy,
Do you have an example Excel file that uses an array to get information from Yahoo that does not return errors. In any spreadsheet tab I am only getting about a dozen rows of data and I have separated the other material after blank rows to separate tabs in the spreadsheet. I am having some of the same trouble reported in other comments, but in addition to quotes I use (from the element tables) 943, 629, 989, 990, and 986 and the quote function with arguments "r" for P/E, , and "t8" for target price. The "Error" returns seem almost random. I would appreciate a way around this. I could get quotes from another source but that does not solve the issue of the other data. Thanks in advance for your help.
Reply to sender . Reply to group . Reply via Web Post . All Messages (11) . Top ^
2g
Re: Get Yahoo quotes
Wed Jan 15, 2014 7:13 am (PST) . Posted by:
rogacox
An example for those of us that are not programmers would be appreciated. Thanks.
Reply to sender . Reply to group . Reply via Web Post . All Messages (11) . Top ^
2h
Re: Get Yahoo quotes
Wed Jan 15, 2014 7:25 am (PST) . Posted by:
mikemcq802
Go to the Files section of the group and look at the SMF-Template- RCHGetYahooQuote s example xls.
It's in the RCHGetYahooQuotes folder in the Files section, Templates and Examples.
I have also had some problems getting yahoo quotes the past week or so. My experience is my array of returned data is either all-there or all-missing. I use the SMFForceRecalculati on to refresh the data when that happens and it almost always returns the full set of data (see the Group's Links section for an explanation) . Yahoo is clearly having issues. The best thing to do is minimize the number of requests you make by using array formulas.
Reply to sender . Reply to group . Reply via Web Post . All Messages (11) . Top ^
2i
Re: Get Yahoo quotes
Wed Jan 15, 2014 7:29 am (PST) . Posted by:
"Randy Harmelink" rharmelink
The RCHGetElementNumber () function can't be array-entered. I thought you
were talking about getting quotes, with RCHGetYahooQuotes( ).
Yahoo's not the only service with such issues. MorningStar was down for a
bit yesterday morning as well.
There is a step-by-step example of setting up an RCHGetYahooQuotes( ) table.
It's pointed to both from the LINKS area of the group *and* the
documentation for RCHGetYahooQuotes( ).
On Wed, Jan 15, 2014 at 8:11 AM, <rogacox@
>
> Do you have an example Excel file that uses an array to get information
> from Yahoo that does not return errors. In any spreadsheet tab I am only
> getting about a dozen rows of data and I have separated the other material
> after blank rows to separate tabs in the spreadsheet. I am having some of
> the same trouble reported in other comments, but in addition to quotes I
> use (from the element tables) 943, 629, 989, 990, and 986 and the quote
> function with arguments "r" for P/E, , and "t8" for target price. The
> "Error" returns seem almost random. I would appreciate a way around this.
> I could get quotes from another source but that does not solve the issue of
> the other data. Thanks in advance for your help.
>
Reply to sender . Reply to group . Reply via Web Post . All Messages (11) . Top ^
2j
Re: Get Yahoo quotes
Wed Jan 15, 2014 7:33 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Exactly. Just as an example, suppose there's a 1% chance of having an
error. If you get 100 pieces of data in an array-entered formula, you have
a 1% chance of failure (of everything). If you get those 100 pieces of data
with individual formulas, you have 63% chance of at least one item failing.
And with smfForceRecalculati on, it's easy to recover from the 1% error. But
if you recalculate with the 100 pieces of data, you once again have a 63%
chance of at least one item failing.
On Wed, Jan 15, 2014 at 8:25 AM, <mikemcq802@
> The best thing to do is minimize the number of requests you make by
> using array formulas.
>
Reply to sender . Reply to group . Reply via Web Post . All Messages (11) . Top ^
2k
Re: Get Yahoo quotes
Wed Jan 15, 2014 8:18 am (PST) . Posted by:
mrothaus
Wonderful! I was searching for a way to use some source other than Yahoo.
For users that have preferred stocks that they look up, be forewarned that Yahoo uses a different ticker symbol than Google. The basic difference is that Yahoo has a "P" in it. For example, AHT series D preferred symbol:
Yahoo: AHT-PD
Google: AHT-D
Reply to sender . Reply to group . Reply via Web Post . All Messages (11) . Top ^
3a
Excel calculating incorrectly
Wed Jan 15, 2014 7:47 am (PST) . Posted by:
"Evin Wrighton" ejw9785
Hello,
I have a spreadsheet that is supposed to auto-calculate several things daily after I enter in the date, one of them is the S&P 500. I'm not sure what went wrong, but it calculated (.57%) on both 1/7 and 1/8 - when it should have been .61% on 1/7 and .02% on 1/8/14.
I noticed on one of your past messages that there was an update on the add-in? Could this be why my spreadsheet has stopped calculating correctly?
I have excel 2007 - I'm not sure what other information you need? This is something new I took over and wasn't expecting to have any issues with the calculating from the RCH_Stock_ Market_Functions excel add-in.
Thank you for your help!!
E
Reply to sender . Reply to group . Reply via Web Post . All Messages (2) . Top ^
3b
Re: Excel calculating incorrectly
Wed Jan 15, 2014 7:51 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Without knowing the details, I can only speculate. Two possibilities come
to mind immediately:
1. You may have your Internet Options set to not get a fresh copy of a web
page every time the site is visited.
2. Since you took it over from someone else, you may have location errors
-- EXCEL not resolving all the links properly because the add-in is in a
different location that the person that saved the workbook.
Both of these issues have entries in the LINKS area of the group.
On Wed, Jan 15, 2014 at 8:41 AM, Evin Wrighton <ejw9785@
>
> I have a spreadsheet that is supposed to auto-calculate several things
> daily after I enter in the date, one of them is the S&P 500. I'm not sure
> what went wrong, but it calculated (.57%) on both 1/7 and 1/8 - when it
> should have been .61% on 1/7 and .02% on 1/8/14.
>
> I noticed on one of your past messages that there was an update on the
> add-in? Could this be why my spreadsheet has stopped calculating correctly?
>
> I have excel 2007 - I'm not sure what other information you need? This is
> something new I took over and wasn't expecting to have any issues with the
> calculating from the RCH_Stock_Market_ Functions excel add-in.
>
> Thank you for your help!!
>
> E
>
Reply to sender . Reply to group . Reply via Web Post . All Messages (2) . Top ^
Visit Your Group
>
View All Topics
>
Create New Topic
>
38 New Members
>
We are making changes based on your feedback, Thank you !
Submit Feedback
>
The Yahoo! Groups Product Blog
Check it out!
>
CHANGE SETTINGS
>
TERMS OF USE
>
UNSUBSCRIBE
>
Wed Jan 15, 2014 1:17 pm (PST) . Posted by:
sihlai
Regarding failure of quotes from Yahoo, is there anybody *NOT* affected by this lately? I remember something similar happened a couple of years ago, but fortunately resolved after a few weeks. Not sure what's going on with Yahoo.
Wed Jan 15, 2014 2:18 pm (PST) . Posted by:
dwstelsel
I have the issue with rchgetyahooquotes().
As a work around I have been using the above mentioned solution to enter the tickers as an array outside of the printable area and then using vlookup() to pull the prices into the table I wish to print.
The array entry is much, much, better because it is only one request to Yahoo instead of the total number of symbols. Still it does error out about 1 in 4 times and I have to re-establish the array by CTL+SHFT+ENTR.
As a work around I have been using the above mentioned solution to enter the tickers as an array outside of the printable area and then using vlookup() to pull the prices into the table I wish to print.
The array entry is much, much, better because it is only one request to Yahoo instead of the total number of symbols. Still it does error out about 1 in 4 times and I have to re-establish the array by CTL+SHFT+ENTR.
Wed Jan 15, 2014 3:28 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Instead of re-entering the array, you can run the smfForceRecalculation
macro. I have it saved as a button on my EXCEL toolbar. See the LINKS area
of the group for more info on the macro.
Also, as I mentioned earlier, I would suggest having RCHGetYahooQuotes()
return a symbol as well, and then use the VLOOKUP() on that column of
returned data.
I didn't realize the error rate was as high as 25% though...
On Wed, Jan 15, 2014 at 3:18 PM, <dwstelsel@yahoo.com> wrote:
> I have the issue with rchgetyahooquotes().
>
> As a work around I have been using the above mentioned solution to enter
> the tickers as an array outside of the printable area and then using
> vlookup() to pull the prices into the table I wish to print.
>
> The array entry is much, much, better because it is only one request to
> Yahoo instead of the total number of symbols. Still it does error out about
> 1 in 4 times and I have to re-establish the array by CTL+SHFT+ENTR.
>
>
macro. I have it saved as a button on my EXCEL toolbar. See the LINKS area
of the group for more info on the macro.
Also, as I mentioned earlier, I would suggest having RCHGetYahooQuotes(
return a symbol as well, and then use the VLOOKUP() on that column of
returned data.
I didn't realize the error rate was as high as 25% though...
On Wed, Jan 15, 2014 at 3:18 PM, <dwstelsel@yahoo.
> I have the issue with rchgetyahooquotes(
>
> As a work around I have been using the above mentioned solution to enter
> the tickers as an array outside of the printable area and then using
> vlookup() to pull the prices into the table I wish to print.
>
> The array entry is much, much, better because it is only one request to
> Yahoo instead of the total number of symbols. Still it does error out about
> 1 in 4 times and I have to re-establish the array by CTL+SHFT+ENTR.
>
>
Wed Jan 15, 2014 5:41 pm (PST) . Posted by:
wdcorpening
Thanks for your suggestion. I have successfully implemented you approach on five excel spreadsheets. The Yahoo! update is now much faster and much more reliable. Excellent suggestion. Thanks.
Wed Jan 15, 2014 2:13 pm (PST) . Posted by:
"Marco Deen" marco.deen
Although functions like =smfGETYAHOOOPTIONQUOTE(A2,"P",$A$1,"ITM1","s") do
not seem to be affected (yet), is it possible (and advisable) to enter
these in an array? And if so how would one do that?
On Wed, Jan 15, 2014 at 10:33 AM, Randy Harmelink <rharmelink@gmail.com>wrote:
>
>
> Exactly. Just as an example, suppose there's a 1% chance of having an
> error. If you get 100 pieces of data in an array-entered formula, you have
> a 1% chance of failure (of everything). If you get those 100 pieces of data
> with individual formulas, you have 63% chance of at least one item failing.
>
> And with smfForceRecalculation, it's easy to recover from the 1% error.
> But if you recalculate with the 100 pieces of data, you once again have a
> 63% chance of at least one item failing.
>
>
> On Wed, Jan 15, 2014 at 8:25 AM, <mikemcq802@yahoo.com> wrote:
>
>> The best thing to do is minimize the number of requests you make by
>> using array formulas.
>>
>
>
>
not seem to be affected (yet), is it possible (and advisable) to enter
these in an array? And if so how would one do that?
On Wed, Jan 15, 2014 at 10:33 AM, Randy Harmelink <rharmelink@gmail.
>
>
> Exactly. Just as an example, suppose there's a 1% chance of having an
> error. If you get 100 pieces of data in an array-entered formula, you have
> a 1% chance of failure (of everything). If you get those 100 pieces of data
> with individual formulas, you have 63% chance of at least one item failing.
>
> And with smfForceRecalculati
> But if you recalculate with the 100 pieces of data, you once again have a
> 63% chance of at least one item failing.
>
>
> On Wed, Jan 15, 2014 at 8:25 AM, <mikemcq802@yahoo.
>
>> The best thing to do is minimize the number of requests you make by
>> using array formulas.
>>
>
>
>
Wed Jan 15, 2014 3:25 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
It's not possible to array-enter that one. In any case, it's actually
retrieving the data with an RCHGetTableCell() function.
BTW, I would advise against using ITMx and OTMx with Yahoo. It's unreliable
whenever they have multiple expiration dates displayed in the same month.
You're better off using the smfGetOptionStrikes() function to determine ITM
and OTM strike prices.
Check out the option quotes example in the LINKS area of the group.
On Wed, Jan 15, 2014 at 3:13 PM, Marco Deen <marco.deen@gmail.com> wrote:
>
> Although functions like =smfGETYAHOOOPTIONQUOTE(A2,"P",$A$1,"ITM1","s") do
> not seem to be affected (yet), is it possible (and advisable) to enter
> these in an array? And if so how would one do that?
>
>
retrieving the data with an RCHGetTableCell(
BTW, I would advise against using ITMx and OTMx with Yahoo. It's unreliable
whenever they have multiple expiration dates displayed in the same month.
You're better off using the smfGetOptionStrikes
and OTM strike prices.
Check out the option quotes example in the LINKS area of the group.
On Wed, Jan 15, 2014 at 3:13 PM, Marco Deen <marco.deen@gmail.
>
> Although functions like =smfGETYAHOOOPTIONQ
> not seem to be affected (yet), is it possible (and advisable) to enter
> these in an array? And if so how would one do that?
>
>
Wed Jan 15, 2014 4:45 pm (PST) . Posted by:
"Kermit W. Prather" kermitpra
Randy, I thought I knew how to do this but have been fighting it for a few
hours. I know the link is correct because if I enter it into a cell and
click on it I get the proper webpage displayed.
The source for the webpage looks like this:
</table><script language="javascript"
src="http://www.dividendchannel.com/safe25/"></script></td>
<td width="18"><img src="/tpixel.gif" width="18" height="18"></td>
<td width="420" valign="top" align="left"><table border="0" width="420"
cellspacing="0" cellpadding="0"><tr>
<td class="menuoff"><span class="tabsmenu" style="color:#FFFFFF">Top Ranked
Dividend Stocks List — Page 1 of
160</span></td>
</tr>
<tr>
<td bgcolor="#FFFFFF" align="left"><img src="/tpixel.gif" WIDTH="420"
HEIGHT="8"><table border="0" cellspacing="0" cellpadding="0" width="100%">
<tr bgcolor="#F6F6F6">
<th><font face="Arial" size="2">Rank</font></th>
<th><font face="Arial" size="2">Symbol</font></th>
<th colspan="2"><font face="Arial" size="2">Dividend</font></th>
<th align="right"><font face="Arial" size="2">Recent
Yield<sup>*</sup> </font></th>
</tr>
<tr>
<td align="center"><font face="Arial" size="2">#<b>1</b></font></td>
<td align="center" width="44%"><font face="Arial" size="2"><a
href="/symbol/cim">CIM</a></font></td>
<td align="right"><font face="Arial" size="2">Q</font></td>
<td align="right"><font face="Arial" size="2">0.36</font></td>
<td align="right"><font face="Arial"
size="2">11.89% </font></td>
</tr>
<tr>
I created the array starting in Cell a3 thru e30 with the formula below. I
checked and each cell has this value. So it appears the array was created.
But all the cells display nothing.
They look like this: empty. So my formula most be wrong but I am stump as to
what formula to put in the array
I tried several other version of he formula and all came up the same blank
table. Apparently, I am not understanding how to read the HTML source and
enter the array formula correctly.
=RCHGetHTMLTable("http://www.dividendchannel.com/dividend-rank/?a=&issuer=&s
ymbol=&sortby=&reverse=&rpp=20&start=0","Top Ranked Dividend Stocks
List",-1,"",1)
hours. I know the link is correct because if I enter it into a cell and
click on it I get the proper webpage displayed.
The source for the webpage looks like this:
</table>
src="http://www.dividend
<td width="
<td width="
cellspacing=
<td class="
Dividend Stocks List — Page 1 of
160</span>
</tr>
<tr>
<td bgcolor="
HEIGHT="
<tr bgcolor="
<th><
<th><
<th colspan="
<th align="
Yield<sup>
</tr>
<tr>
<td align="
<td align="
href="/
<td align="
<td align="
<td align="
size="2"
</tr>
<tr>
I created the array starting in Cell a3 thru e30 with the formula below. I
checked and each cell has this value. So it appears the array was created.
But all the cells display nothing.
They look like this: empty. So my formula most be wrong but I am stump as to
what formula to put in the array
I tried several other version of he formula and all came up the same blank
table. Apparently, I am not understanding how to read the HTML source and
enter the array formula correctly.
=RCHGetHTMLTable(
ymbol=&sortby=
List",-
Wed Jan 15, 2014 5:03 pm (PST) . Posted by:
mikemcq802
Kermit, your search phrase was poor - it was found multiple times on that page.
Try this:
=RCHGetHTMLTable("http://www.dividendchannel.com/dividend-rank/?a=&issuer=&symbol=&sortby=&reverse=&rpp=20&start=0","dividendchannel.com/safe25",-1,"",1)
Try this:
=RCHGetHTMLTable(
Wed Jan 15, 2014 5:39 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
As noted, not a good search term. And you're going backward from it, when
it's actually outside of the table? I usually try to use a column heading
within the table, if I can make it unique, and then go backwards and
forwards from it. For example:
=RCHGetHTMLTable("
http://www.dividendchannel.com/dividend-rank/?a=&issuer=&symbol=&sortby=&reverse=&rpp=20&start=0",">Recent
Yield",-1,"",1)
On Wed, Jan 15, 2014 at 5:45 PM, Kermit W. Prather
<kermitp@tampabay.rr.com>wrote:
>
>
> Randy, I thought I knew how to do this but have been fighting it for a few
> hours. I know the link is correct because if I enter it into a cell and
> click on it I get the proper webpage displayed.
>
>
>
>
> I created the array starting in Cell a3 thru e30 with the formula below. I
> checked and each cell has this value. So it appears the array was created.
>
> But all the cells display nothing.
>
> They look like this: empty. So my formula most be wrong but I am stump as
> to what formula to put in the array
>
>
>
> I tried several other version of he formula and all came up the same
> blank table. Apparently, I am not understanding how to read the HTML source
> and enter the array formula correctly.
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> =RCHGetHTMLTable("
> http://www.dividendchannel.com/dividend-rank/?a=&issuer=&symbol=&sortby=&reverse=&rpp=20&start=0","Top
> Ranked Dividend Stocks List",-1,"",1)
>
it's actually outside of the table? I usually try to use a column heading
within the table, if I can make it unique, and then go backwards and
forwards from it. For example:
=RCHGetHTMLTable(
http://www.dividend
Yield",
On Wed, Jan 15, 2014 at 5:45 PM, Kermit W. Prather
<kermitp@tampabay.
>
>
> Randy, I thought I knew how to do this but have been fighting it for a few
> hours. I know the link is correct because if I enter it into a cell and
> click on it I get the proper webpage displayed.
>
>
>
>
> I created the array starting in Cell a3 thru e30 with the formula below. I
> checked and each cell has this value. So it appears the array was created.
>
> But all the cells display nothing.
>
> They look like this: empty. So my formula most be wrong but I am stump as
> to what formula to put in the array
>
>
>
> I tried several other version of he formula and all came up the same
> blank table. Apparently, I am not understanding how to read the HTML source
> and enter the array formula correctly.
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> =RCHGetHTMLTable(
> http://www.dividend
> Ranked Dividend Stocks List",-
>
Wed Jan 15, 2014 10:28 pm (PST) . Posted by:
babond2
I have tried everything I can think of, to get this template to work:All I get are blank charts and #Name? in almost all cells in the Data Sheet.
1- Used "SMF FIXLINKS (but, file is protected-needs password)
2- Installed : SMF Add-In 2.1.2012.12.29 in "Program Files" directory.
3- Rebooted
4- Installed: SMF Add-In 2.1.2012.12.29 in "C:\ directory.
5- Rebooted
6- Downloaded about 6 copies of the template (insuring it is fresh)
Does anyone have any other ideas.
"SMF FIXLINKS " has always been able to correct any problem I have had with incompatiblity. I have been able to Unprotect (almost) any other template and "SMF FIXLINKS " has instantly corrected problems.
1- Used "SMF FIXLINKS (but, file is protected-needs password)
2- Installed : SMF Add-In 2.1.2012.12.
3- Rebooted
4- Installed: SMF Add-In 2.1.2012.12.
5- Rebooted
6- Downloaded about 6 copies of the template (insuring it is fresh)
Does anyone have any other ideas.
"SMF FIXLINKS " has always been able to correct any problem I have had with incompatiblity. I have been able to Unprotect (almost) any other template and "SMF FIXLINKS " has instantly corrected problems.
Wed Jan 15, 2014 10:37 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Not sure what to tell you. I opened the file from the Yahoo group. I had
location errors as well, because it was created when I had the add-in in a
different location. I ran smfFixLinks, and everything worked fine.
From what I could see, the file is not protected and shouldn't need a
password.
On Wed, Jan 15, 2014 at 11:11 PM, <babond@suddenlink.net> wrote:
> I have tried everything I can think of, to get this template to work:All
> I get are *blank* charts and *#Name?* in almost all cells in the Data
> Sheet.
>
> 1- Used "SMF FIXLINKS (but, file is protected-needs password)
>
> 2- Installed : SMF Add-In 2.1.2012.12.29 in "Program Files" directory.
>
> 3- Rebooted
>
> 4- Installed: SMF Add-In 2.1.2012.12.29 in "C:\ directory.
>
> 5- Rebooted
>
> 6- Downloaded about 6 copies of the template (insuring it is fresh)
>
> Does anyone have any other ideas.
>
> "SMF FIXLINKS " has always been able to correct any problem I have had
> with incompatiblity. I have been able to Unprotect (almost) any other
> template and "SMF FIXLINKS " has instantly corrected problems.
>
location errors as well, because it was created when I had the add-in in a
different location. I ran smfFixLinks, and everything worked fine.
From what I could see, the file is not protected and shouldn'
password.
On Wed, Jan 15, 2014 at 11:11 PM, <babond@suddenlink.
> I have tried everything I can think of, to get this template to work:All
> I get are *blank* charts and *#Name?* in almost all cells in the Data
> Sheet.
>
> 1- Used "SMF FIXLINKS (but, file is protected-needs password)
>
> 2- Installed : SMF Add-In 2.1.2012.12.
>
> 3- Rebooted
>
> 4- Installed: SMF Add-In 2.1.2012.12.
>
> 5- Rebooted
>
> 6- Downloaded about 6 copies of the template (insuring it is fresh)
>
> Does anyone have any other ideas.
>
> "SMF FIXLINKS " has always been able to correct any problem I have had
> with incompatiblity. I have been able to Unprotect (almost) any other
> template and "SMF FIXLINKS " has instantly corrected problems.
>
Tidak ada komentar:
Posting Komentar