Messages In This Digest (25 Messages)
- 1a.
- Re: Speed-up process time From: Kermit W. Prather
- 2a.
- Value errors From: mrmonopoly88
- 2b.
- Re: Value errors From: mrmonopoly88
- 2c.
- Re: Value errors From: James Nylen
- 2d.
- Re: Value errors From: Randy Harmelink
- 2e.
- Re: Value errors From: mrmonopoly88
- 2f.
- Re: Value errors From: Randy Harmelink
- 2g.
- Re: Value errors From: mrmonopoly88
- 2h.
- Re: Value errors From: mrmonopoly88
- 2i.
- Re: Value errors From: Dan Hennessy
- 2j.
- Re: Value errors From: Randy Harmelink
- 3a.
- Re: SMF spreadsheet for MOS from "Rule #1:..." by Phil Town From: mrmonopoly88
- 3b.
- Re: SMF spreadsheet for MOS from "Rule #1:..." by Phil Town From: Randy Harmelink
- 4a.
- smfUpdateDownloadTable From: carmine288
- 4b.
- Re: smfUpdateDownloadTable From: Randy Harmelink
- 4c.
- Re: smfUpdateDownloadTable From: carmine288
- 4d.
- Re: smfUpdateDownloadTable From: Randy Harmelink
- 5a.
- Ignore #value errors, data retrieval error & windows security messag From: Duckwhisperer
- 5b.
- Re: Ignore #value errors, data retrieval error & windows security me From: Randy Harmelink
- 5c.
- Re: Ignore #value errors, data retrieval error & windows security me From: Duckwhisperer
- 5d.
- Re: Ignore #value errors, data retrieval error & windows security me From: Randy Harmelink
- 6a.
- Problem with Array From: Troy
- 6b.
- Re: Problem with Array From: Randy Harmelink
- 7a.
- Re: Yahoo Quote failures - work-around? From: Market Monk
- 7b.
- Re: Yahoo Quote failures - work-around? From: Market Monk
Messages
- 1a.
-
Re: Speed-up process time
Posted by: "Kermit W. Prather" kermitp@tampabay.rr.com kermitpra
Thu Aug 11, 2011 6:52 am (PDT)
I never understand why folks want to build these complex spreadsheet to create a rating system that is already done for you. Plus, it is totally free.
The link below will take you to MSN's stockScouter's rating where it shows you how many stocks fit each rating. Simply click on the rating bar and you will get a page where you can select the criteria you want listed
http://moneycentral.msn.com/ investor/ StockRating/ srsmain.asp
http://moneycentral.msn.com/ This link gives you all 132 stocks rate a 10 the highest score.investor/ StockRating/ srstopstocksresu lts.aspx? sco=10
You can then download this to a spreadsheet. Unfortunately, I don't think you can use the SMF Add-in.
Maybe Randy tell us if it would work with the add-in.
Kermit
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com ] On Behalf Of Randy Harmelink
Sent: Wednesday, August 10, 2011 4:27 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Speed-up process time
Sorry. The add-in just isn't intended for that level of use. And, personally, I don't like to see people using it to access the free data sources so heavily.
The best thing you can do is come up with a simple screen that can narrow down your company list to the meaningful contenders, FIRST, before using the add-in to get more detailed information. In essence, a pre-scorecard scorecard.
On Wed, Aug 10, 2011 at 12:05 PM, steven_rourk <steven_rourk@hotmail.com > wrote:
Hi Randy, I was hopping you can give me advice with something.
I have an Excel file for each company i follow, with 8 years worth of balance sheets, several finantial ratios, several price ratios, industry ratios, etc, etc.. much of the info is uploaded with your great Addin product, saving me a lot of time (before that I fed the spreadsheets manually!..Thanks Randy!). All this ratios/info results in a "score" for the company. Then I have another "group" file were I group all the tickers with their respective "score" and some other info, and sort them out, so I can do research only on the companies that have a good scorecards, and not waste time with companies with poor scorecards (i.e, poor financials, poor ratios, price too high, etc)... Each company file takes about 20-25 seconds to open, as it retreives info from several diferents web pages (Yahoo finance, MNS, ADVFN, Morningstar, etc) and the group file takes about 10 seconds as it is fed with links to each of the company files. I have over 500 company files. So in order to update the hole thing I have to first open the group file, and then open the 500+ company files, and thus the hole process takes more than 3 hours.... And it complicates more because after I open 30 files, an erros pops up saying i have too many web pages open, so I have to do this process 30 files at a time, I open 30, save, close, and then open 30 other files, save, close, etc, etc...
So I was wondering if you have a tip for me so I can speed up this process.
- 2a.
-
Value errors
Posted by: "mrmonopoly88" montegnies.dennis@gmail.com mrmonopoly88
Thu Aug 11, 2011 7:48 am (PDT)
Good day.
I have a Rule # 1 spreadsheet but it is not working very well. I get alot of errors and value errors. It depends even on what ticker I fill in.
By example, inside ownership (=RCHGetElementNumber(C3;10) /10000) works with google but not with apple.
Example Google:
http://imagenic.net/viewer. php?file= wcul4t70efx0i201 pvas.jpg
Example Apple:
http://imagenic.net/viewer. php?file= r7lta9pdq1uggfw7 i15.jpg
I'll try uploading an attachement.
- 2b.
-
Re: Value errors
Posted by: "mrmonopoly88" montegnies.dennis@gmail.com mrmonopoly88
Thu Aug 11, 2011 7:52 am (PDT)
The excel file:
http://f1.grp.yahoofs.com/ v1/YOBDTuet3TMTr 8sKrqqaDrfKB3GfN be5YaES0clUVH- P-fnQnS_ELv9DK1t ULW5G6-7uhblVcbz uFf3NvKLJUyMdZxv 7-_OlAThqtD6LO8T 7xTxFyISJjdF_ /Uploads% 20by%20forum% 20members/ RULE%20%231. xlsx
--- In smf_addin@yahoogroups.com , "mrmonopoly88" <montegnies.dennis@.. .> wrote:
>
> Good day.
>
> I have a Rule # 1 spreadsheet but it is not working very well. I get alot of errors and value errors. It depends even on what ticker I fill in.
>
> By example, inside ownership (=RCHGetElementNumber(C3;10) /10000) works with google but not with apple.
>
> Example Google:
>
> http://imagenic.net/viewer. php?file= wcul4t70efx0i201 pvas.jpg
>
> Example Apple:
>
> http://imagenic.net/viewer. php?file= r7lta9pdq1uggfw7 i15.jpg
>
> I'll try uploading an attachement.
>
- 2c.
-
Re: Value errors
Posted by: "James Nylen" jnylen@gmail.com jamesnylen
Thu Aug 11, 2011 7:54 am (PDT)
Those are the worst ads I've ever seen on an image hosting site. You should
use imgur.com instead.
On Thu, Aug 11, 2011 at 10:48 AM, mrmonopoly88
<montegnies.dennis@gmail.com >wrote:
> **
>
>
> Good day.
>
> I have a Rule # 1 spreadsheet but it is not working very well. I get alot
> of errors and value errors. It depends even on what ticker I fill in.
>
> By example, inside ownership (=RCHGetElementNumber(C3;10) /10000) works with
> google but not with apple.
>
> Example Google:
>
> http://imagenic.net/viewer. php?file= wcul4t70efx0i201 pvas.jpg
>
> Example Apple:
>
> http://imagenic.net/viewer. php?file= r7lta9pdq1uggfw7 i15.jpg
>
> I'll try uploading an attachement.
>
>
>
- 2d.
-
Re: Value errors
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Thu Aug 11, 2011 8:22 am (PDT)
Not sure what to tell you, as both of your examples below work fine here...
When I looked at the #VALUE! errors I get from using your workbook, it was
because the data providers were returning "N/A" values for the data elements
-- which cause a #VALUE! error when you try to do some mathematical
operation with them.
On Thu, Aug 11, 2011 at 7:48 AM, mrmonopoly88
<montegnies.dennis@gmail.com >wrote:
>
> I have a Rule # 1 spreadsheet but it is not working very well. I get alot
> of errors and value errors. It depends even on what ticker I fill in.
>
> By example, inside ownership (=RCHGetElementNumber(C3;10) /10000) works with
> google but not with apple.
>
> Example Google:
>
> http://imagenic.net/viewer. php?file= wcul4t70efx0i201 pvas.jpg
>
> Example Apple:
>
> http://imagenic.net/viewer. php?file= r7lta9pdq1uggfw7 i15.jpg
>
> I'll try uploading an attachement.
>
- 2e.
-
Re: Value errors
Posted by: "mrmonopoly88" montegnies.dennis@gmail.com mrmonopoly88
Thu Aug 11, 2011 9:59 am (PDT)
What can I do about those N/A values?
And what can I do about cash/free cash errors?
Thanks
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> Not sure what to tell you, as both of your examples below work fine here...
>
> When I looked at the #VALUE! errors I get from using your workbook, it was
> because the data providers were returning "N/A" values for the data elements
> -- which cause a #VALUE! error when you try to do some mathematical
> operation with them.
>
> On Thu, Aug 11, 2011 at 7:48 AM, mrmonopoly88
> <montegnies.dennis@.. .>wrote:
>
> >
> > I have a Rule # 1 spreadsheet but it is not working very well. I get alot
> > of errors and value errors. It depends even on what ticker I fill in.
> >
> > By example, inside ownership (=RCHGetElementNumber(C3;10) /10000) works with
> > google but not with apple.
> >
> > Example Google:
> >
> > http://imagenic.net/viewer. php?file= wcul4t70efx0i201 pvas.jpg
> >
> > Example Apple:
> >
> > http://imagenic.net/viewer. php?file= r7lta9pdq1uggfw7 i15.jpg
> >
> > I'll try uploading an attachement.
> >
>
- 2f.
-
Re: Value errors
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Thu Aug 11, 2011 10:39 am (PDT)
"N/A" is what the data source is providing. The only alternative would be to
use a different source for the data. However, you really shouldn't mix data
sources -- you may find your years of data mismatching.
I'm not getting them. Do you maybe have this AdvFN issue:
http://finance.groups.yahoo. com/group/ smf_addin/ message/10464
On Thu, Aug 11, 2011 at 9:58 AM, mrmonopoly88
<montegnies.dennis@gmail.com >wrote:
> What can I do about those N/A values?
>
> And what can I do about cash/free cash errors?
>
- 2g.
-
Re: Value errors
Posted by: "mrmonopoly88" montegnies.dennis@gmail.com mrmonopoly88
Thu Aug 11, 2011 11:38 am (PDT)
The errors are solved now, thanks! Still getting N/A though :(
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> "N/A" is what the data source is providing. The only alternative would be to
> use a different source for the data. However, you really shouldn't mix data
> sources -- you may find your years of data mismatching.
>
> I'm not getting them. Do you maybe have this AdvFN issue:
>
> http://finance.groups.yahoo. com/group/ smf_addin/ message/10464
>
> On Thu, Aug 11, 2011 at 9:58 AM, mrmonopoly88
> <montegnies.dennis@.. .>wrote:
>
> > What can I do about those N/A values?
> >
> > And what can I do about cash/free cash errors?
> >
>
- 2h.
-
Re: Value errors
Posted by: "mrmonopoly88" montegnies.dennis@gmail.com mrmonopoly88
Thu Aug 11, 2011 11:57 am (PDT)
I do not understand why I get a value error by example when using:
=RCHGetElementNumber(C3;13931) /10
(current P/E ratio by Morningstar). It says Value error.
However, I can find it easily by browsing the website:
http://financials.morningstar. com/valuation/ price-ratio. html?t=AAPL& region=USA& culture=en- us
Works "=RCHGetElementNumber(C3;13931) /10" for you for AAPL ticker?
- 2i.
-
Re: Value errors
Posted by: "Dan Hennessy" danhennessy@hotmail.com danhennessy
Thu Aug 11, 2011 12:26 pm (PDT)
If you are logged in to morningstar you will get an error. Log out (and reboot if needed).
Sent from my Verizon Wireless BlackBerry
-----Original Message-----
From: mrmonopoly88 <montegnies.dennis@gmail.com >
Date: Thu, 11 Aug 2011 18:57:21
To: <smf_addin@yahoogroups.com >
Subject: [smf_addin] Re: Value errors
�
I do not understand why I get a value error by example when using:
=RCHGetElementNumber(C3;13931) /10
(current P/E ratio by Morningstar). It says Value error.
However, I can find it easily by browsing the website:
http://financials.morningstar. com/valuation/ price-ratio. html?t=AAPL& amp;region= USA&culture= en-us
Works "=RCHGetElementNumber(C3;13931) /10" for you for AAPL ticker?
- 2j.
-
Re: Value errors
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Thu Aug 11, 2011 12:36 pm (PDT)
That's one of the web pages that Morningstar recently redesigned -- even the
URL has changed. The data is no longer delivered in the source code of the
web page, so the add-in can no longer access it.
You can still pick it up off their print report page though:
=RCHGetTableCell("
http://quicktake.morningstar. com/stocknet/ printreport. aspx?symbol= AAPL
",1,">Price/Earnings" )
On Thu, Aug 11, 2011 at 11:57 AM, mrmonopoly88
<montegnies.dennis@gmail.com >wrote:
> I do not understand why I get a value error by example when using:
>
> =RCHGetElementNumber(C3;13931) /10
> (current P/E ratio by Morningstar). It says Value error.
>
> However, I can find it easily by browsing the website:
>
> http://financials.morningstar. com/valuation/ price-ratio. html?t=AAPL& region=USA& culture=en- us
>
> Works "=RCHGetElementNumber(C3;13931) /10" for you for AAPL ticker?
>
>
- 3a.
-
Re: SMF spreadsheet for MOS from "Rule #1:..." by Phil Town
Posted by: "mrmonopoly88" montegnies.dennis@gmail.com mrmonopoly88
Thu Aug 11, 2011 10:01 am (PDT)
Where can I contact the owners? I have trouble making my own and I would really like to use such template.
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> It wasn't my workbook, so I can't really distribute it.
>
> On Mon, Jul 25, 2011 at 11:16 PM, mrmonopoly88
> <montegnies.dennis@.. .>wrote:
>
> > Do you have any backup or idea where I can get the spreadsheet with
> > SMF-addin for Phil Town Rule #1 strategy?
> >
>
- 3b.
-
Re: SMF spreadsheet for MOS from "Rule #1:..." by Phil Town
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Thu Aug 11, 2011 10:52 am (PDT)
Sorry, but I have no idea who how to contact the owners. I was a little
blind-sided when the forum disappeared.
However, they had many of the same issues, which is why I barely used the
template. I would suggest getting all of the components, and then adding
error-checking as the data is retrieved, to determine how you want to handle
the missing data. If you can restrict the data elements to a single data
source (e.g. AdvFN), I think it would be to your advantage.
The element I defined for it uses all MSN data elements, but they've not
been populating the average P/E ratios. I think because many companies have
had negative earnings in recent years. A special situation that needs to be
handled. Their way is to tag the data element as "N/A".
On Thu, Aug 11, 2011 at 10:00 AM, mrmonopoly88
<montegnies.dennis@gmail.com >wrote:
> Where can I contact the owners? I have trouble making my own and I would
> really like to use such template.
>
- 4a.
-
smfUpdateDownloadTable
Posted by: "carmine288" carmine.nicoletta@gmail.com carmine288
Thu Aug 11, 2011 11:44 am (PDT)
This is driving me mad... I can't get smfUpdateDownloadTable to work.
I have read all the threads I could find on smfUpdateDownloadTable.
I followed:
http://finance.groups.yahoo. com/group/ smf_addin/ message/3027
and
http://finance.groups.yahoo. com/group/ smf_addin/ message/8832
to the letter, and I still cannot get the smfUpdateDownloadTable macro to run.
All other examples:
smfUpdateDownloadTable-Sample. xls
smfUpdateDownloadTable-Option- Quotes.xls
Test-tbarber4-smfUpdateDownloa dTable-AdvFN. xls
Test-tbarber4-smfUpdateDownloa dTable.xls
work fine.
I uploaded my example "My-smfUpdateDownloadTableExample. xlsx
" to the Files area "Uploads by forum members"
I feel I'm missing an enable macro setting somewhere. I'm using Excel 2007
- 4b.
-
Re: smfUpdateDownloadTable
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Thu Aug 11, 2011 12:29 pm (PDT)
You just need to NAME cell B3 "Ticker" (not just put "Ticker" in the content
of the cell). Once I did that, your workbook worked fine. Everything keys
off that range name.
On Thu, Aug 11, 2011 at 11:43 AM, carmine288 <carmine.nicoletta@gmail.com >wrote:
> This is driving me mad... I can't get smfUpdateDownloadTable to work.
>
> I have read all the threads I could find on smfUpdateDownloadTable.
> I followed:
> http://finance.groups.yahoo. com/group/ smf_addin/ message/3027
> and
> http://finance.groups.yahoo. com/group/ smf_addin/ message/8832
> to the letter, and I still cannot get the smfUpdateDownloadTable macro to
> run.
>
> All other examples:
> smfUpdateDownloadTable-Sample. xls
> smfUpdateDownloadTable-Option- Quotes.xls
> Test-tbarber4-smfUpdateDownloa dTable-AdvFN. xls
> Test-tbarber4-smfUpdateDownloa dTable.xls
> work fine.
>
> I uploaded my example "My-smfUpdateDownloadTableExample. xlsx
> " to the Files area "Uploads by forum members"
>
> I feel I'm missing an enable macro setting somewhere. I'm using Excel 2007
>
- 4c.
-
Re: smfUpdateDownloadTable
Posted by: "carmine288" carmine.nicoletta@gmail.com carmine288
Thu Aug 11, 2011 12:42 pm (PDT)
Man, do I feel dumb...
You're the best, thanks!
BTW, in some of the exapmles I see something like:
RCHGetElementNumber("~~~~~", 941)
Why do that? Isn't 941 simpler?
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> You just need to NAME cell B3 "Ticker" (not just put "Ticker" in the content
> of the cell). Once I did that, your workbook worked fine. Everything keys
> off that range name.
>
> On Thu, Aug 11, 2011 at 11:43 AM, carmine288 <carmine.nicoletta@...>wrote:
>
> > This is driving me mad... I can't get smfUpdateDownloadTable to work.
> >
> > I have read all the threads I could find on smfUpdateDownloadTable.
> > I followed:
> > http://finance.groups.yahoo. com/group/ smf_addin/ message/3027
> > and
> > http://finance.groups.yahoo. com/group/ smf_addin/ message/8832
> > to the letter, and I still cannot get the smfUpdateDownloadTable macro to
> > run.
> >
> > All other examples:
> > smfUpdateDownloadTable-Sample. xls
> > smfUpdateDownloadTable-Option- Quotes.xls
> > Test-tbarber4-smfUpdateDownloa dTable-AdvFN. xls
> > Test-tbarber4-smfUpdateDownloa dTable.xls
> > work fine.
> >
> > I uploaded my example "My-smfUpdateDownloadTableExample. xlsx
> > " to the Files area "Uploads by forum members"
> >
> > I feel I'm missing an enable macro setting somewhere. I'm using Excel 2007
> >
>
- 4d.
-
Re: smfUpdateDownloadTable
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Thu Aug 11, 2011 12:51 pm (PDT)
On Thu, Aug 11, 2011 at 12:41 PM, carmine288 <carmine.nicoletta@gmail.com >wrote:
>
> BTW, in some of the exapmles I see something like:
> RCHGetElementNumber("~~~~~", 941)
>
> Why do that? Isn't 941 simpler?
>
It is simpler for that example, yes. But it was just meant to show that you
can use any of the add-in functions, in their raw form, substituting the
five tildas for the ticker symbol. The latest beta version of the add-in
also lets you refer back to previous retrieved columns.
Some examples from a workbook I created earlier this year, which uses some
complicated formulas for the update table:
*Ex-Div Date #1* DATEVALUE(RCHGetTableCell("
http://www.earnings.com/company. ",2,">Amount"asp?client= cb&ticker= ~~~~~ ,">~~~~~
<")) *Ex-Div Date #2* DATEVALUE(RCHGetTableCell("
http://www.earnings.com/company. ",2,">Amount"asp?client= cb&ticker= ~~~~~ ,">~~~~~
<",">~~~~~ <")) *Ex-Div Date #3* DATEVALUE(RCHGetTableCell("
http://www.earnings.com/company. ",2,">Amount"asp?client= cb&ticker= ~~~~~ ,">~~~~~
<",">~~~~~ <",">~~~~~ <")) *Ex-Div Date*
IF(LEN(~~~3~~~)=0,"", ~~~3~~~) *Dividend
Amount* RCHGetTableCell("
http://www.earnings.com/company. ",1,">Amount"asp?client= cb&ticker= ~~~~~ ,">~~~~~
<") *Last Price* RCHGetYahooQuotes("~~~~~"," l1") *Percent Change*
RCHGetYahooQuotes("~~~~~"," p2") *#1 Prior
Ex-Div Date* IF(~~~4~~~<TODAY(),~~~7~~~ ,~~~6~~~) *#1 Peak*
MAX(RCHGetYahooHistory("~~~~ ~",YEAR(~ ~~1~~~-10) ,MONTH(~~ ~1~~~-10) ,DAY(~~~1~ ~~-10),YEAR( ~~~1~~~-1) ,MONTH(~~ ~1~~~-1), DAY(~~~1~ ~~-1),,"H" ,0,,,5,1) )
*#2 Prior
Ex-Div Date* IF(~~~6~~~<TODAY(),~~~8~~~ ,~~~7~~~) *#2 Peak*
MAX(RCHGetYahooHistory("~~~~ ~",YEAR(~ ~~1~~~-10) ,MONTH(~~ ~1~~~-10) ,DAY(~~~1~ ~~-10),YEAR( ~~~1~~~-1) ,MONTH(~~ ~1~~~-1), DAY(~~~1~ ~~-1),,"H" ,0,,,5,1) )
*Stock Scouter* 543 *Risk Grade* 552 *Analyst Rating* 353
Something like "~~~3~~~" says to get the value from 3 columns prior to the
current column and substitute it into the formula.
- 5a.
-
Ignore #value errors, data retrieval error & windows security messag
Posted by: "Duckwhisperer" duckwhispering@yahoo.com duckwhispering
Thu Aug 11, 2011 12:26 pm (PDT)
Hello!
First of all, you're doing great work!
I'm trying to make a spreadsheet myself based on your add-in. But I'm encoutering some problems.
1) For certain stocks some data for a couple of years is not around, but for other years it is, so I get a row of certain values and then occasionally #value errors in that same row.
http://i56.tinypic.com/8vomme. png
And because of that, I can't calculate my other formulas.
So my question is, is there a way to ignore these value errors so the formula can be calculated on the data that is left?
2) Another thing is, I have given my spreadsheet to a friend, but with cash and free cash (in the range 6575-6605) he gets "error". So simply put, the data can't be retrieved, whilest I can. What can cause this?
3) Every time I put in a new ticker symbol I get this "windows security message". Can this be ignored forever?
Thanks in advance!
- 5b.
-
Re: Ignore #value errors, data retrieval error & windows security me
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Thu Aug 11, 2011 12:40 pm (PDT)
1. You'll need to use a "work range" so that you can handle any
unexpected data that gets returned. That is, return the raw data in one
range (i.e. "work") and then create the calculation values from the range.
2. Check out the AdvFN link in the "Links" area of the group. That may be
the problem?
3. I've never seen the message, so can't answer the question. Might be
related to (2) above?
On Thu, Aug 11, 2011 at 11:50 AM, Duckwhisperer <duckwhispering@yahoo.com >wrote:
>
> I'm trying to make a spreadsheet myself based on your add-in. But I'm
> encoutering some problems.
>
> 1) For certain stocks some data for a couple of years is not around, but
> for other years it is, so I get a row of certain values and then
> occasionally #value errors in that same row.
> http://i56.tinypic.com/8vomme. png
> And because of that, I can't calculate my other formulas.
> So my question is, is there a way to ignore these value errors so the
> formula can be calculated on the data that is left?
>
> 2) Another thing is, I have given my spreadsheet to a friend, but with cash
> and free cash (in the range 6575-6605) he gets "error". So simply put, the
> data can't be retrieved, whilest I can. What can cause this?
>
> 3) Every time I put in a new ticker symbol I get this "windows security
> message". Can this be ignored forever?
>
- 5c.
-
Re: Ignore #value errors, data retrieval error & windows security me
Posted by: "Duckwhisperer" duckwhispering@yahoo.com duckwhispering
Thu Aug 11, 2011 12:49 pm (PDT)
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> 1. You'll need to use a "work range" so that you can handle any
> unexpected data that gets returned. That is, return the raw data in one
> range (i.e. "work") and then create the calculation values from the range.
Can you give an example? Because I'm not used to working with excel and can't figure it out in my head.
Thanks.
- 5d.
-
Re: Ignore #value errors, data retrieval error & windows security me
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Thu Aug 11, 2011 12:59 pm (PDT)
Just retrieve the raw data in one row.
Then, in the next row, you'll need IF() statements to verify that what
you're getting back is usable or not. For example, you might do:
=IF(D10="N/A",0,D10/10)
...so that you'll substitute a zero value instead of the "N/A". Then you
just use the new row for your calculations, where the new row is either a
copy of the "work" row, or a modified version that has the values you want
to substitute if you're not getting something usable back.
Or, another example -- I do recall that one of the "edits" of the Rule #1
workbooks from the forum wanted to make sure that a P/E ratio wasn't so
outrageous that it would skew the calculation. So, the raw P/E values were
retrieved in the "work" row. In the row used for calculations, a max value
of 50 was placed on it. That way, a P/E of 300 for one year wouldn't throw
everything off-kilter. You could use whatever edit(s) you feel comfortable
with.
And, as I noted, some of the "N/A" values for P/E might be because earnings
were negative for that year, or for some year of a 5-year average. The
question would be if you returned the P/E for 5 consecutive years and one
was "N/A", what would you assign for the 5-year average.
Basically, a "work" range is just a way for you to edit the data, either for
reasonableness or for actual value.
On Thu, Aug 11, 2011 at 12:49 PM, Duckwhisperer <duckwhispering@yahoo.com >wrote:
>
> --- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
> >
> > 1. You'll need to use a "work range" so that you can handle any
> > unexpected data that gets returned. That is, return the raw data in
> one
> > range (i.e. "work") and then create the calculation values from the
> range.
>
>
> Can you give an example? Because I'm not used to working with excel and
> can't figure it out in my head.
>
> Thanks.
>
>
>
>
>
>
>
> --------------------- --------- ------
>
> Yahoo! Groups Links
>
>
>
>
- 6a.
-
Problem with Array
Posted by: "Troy" troy.mills@tx.rr.com troyamills
Thu Aug 11, 2011 3:49 pm (PDT)
Randy,
Trying to build a very simple array to obtain closing price quotes on about 80 stocks. Here's the formula I'm using:
=RCHGetYahooQuotes(B2:B81, L1,,,NOW())
Here's the result: #NUM!
Any ideas as to the source of my problem?
Thanks,
Troy
- 6b.
-
Re: Problem with Array
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Thu Aug 11, 2011 4:24 pm (PDT)
The second parameter should be a string literal of "l1" (in quotes). As is,
you are telling the function to use the contents of cell L1.
On Thu, Aug 11, 2011 at 3:49 PM, Troy <troy.mills@tx.rr.com > wrote:
>
> Trying to build a very simple array to obtain closing price quotes on about
> 80 stocks. Here's the formula I'm using:
>
> =RCHGetYahooQuotes(B2:B81, L1,,,NOW())
>
> Here's the result: #NUM!
>
> Any ideas as to the source of my problem?
>
- 7a.
-
Re: Yahoo Quote failures - work-around?
Posted by: "Market Monk" marketmonk777@yahoo.com marketmonk777
Thu Aug 11, 2011 9:14 pm (PDT)
Are you getting this "old" data for a single ticker, the same ticker(s), or random tickers?
Are you trying to obtain a single quote (latest day) or multiple days?
--- In smf_addin@yahoogroups.com , "MikeM" <mikemcq802@...> wrote:
>
> I have also been experiencing Yahoo Quote accuracy problems over the past couple weeks. I have a couple spreadsheets I've used for more than a year which retrieve array-entered quotes. These have worked flawlessly until recently.
>
> I get a quote, it's just not always today's quote. It's not unusual to get one from a week ago - or a week ending quote rather than the current.
>
> Once I realized the quotes weren't random but valid quotes just for the wrong day I now retrieve the date in the quote request to ensure its the day I expect. E.G., today, on Aug 10 it's not unusual to get quotes returned with an Aug 2 date. The quote is right for Aug 2, it's just not supposed to be returned on the 10th.
>
> I use Randy's "refresh" macro and have that as a button in the spreadsheet to speed re-tries. It usually only takes a couple clicks to get all the quotes correct. Annoying but fairly quick.
>
> In one spreadsheet I have 4 separate array-entered quote retrieval blocks. Interestingly, an array of quotes is either all-right or all-wrong. But, it's not unusual to have 1 array of quotes be from a different date than another array of quotes in the same spreadsheet.
>
> I have IE's cache set to "Every time I visit page" per Randy's documentation.
>
> I cannot mimic this failure using IE and downloading CSV quotes. If I could, I'd submit it to Yahoo!
>
- 7b.
-
Re: Yahoo Quote failures - work-around?
Posted by: "Market Monk" marketmonk777@yahoo.com marketmonk777
Thu Aug 11, 2011 9:18 pm (PDT)
Outside of Yahoo, folks can get quotes from Google and MSN Finance. Not sure if add-in works for those so a separate downloader would be required.
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> I don't know of any work-around, other than getting a pay service. There
> isn't another free service that I know of that returns quotes for a
> multitude of tickers in a single Internet access.
>
> If you are getting a number of quotes, getting them by individual ticker
> will take a long time.
>
> On Wed, Aug 10, 2011 at 6:57 PM, Daniel Perciballi <dperciballi@...>wrote:
>
> >
> > THAN K GOD someone else is having this problem. It started for me about a
> > month ago (working flawlessly prior to that). Today I am continually
> > getting quotes from Aug 2nd as well.
> >
> > What is your workaround? You request the date as welll and wait until all
> > the dates are "today"?
> >
> > I also retrieve blocks of quotes in the same sheet and refresh accordingly.
> >
>
Need to Reply?
Click one of the "Reply" links to respond to a specific message in the Daily Digest.
Change settings via the Web (Yahoo! ID required)
Change settings via email: Switch delivery to Individual | Switch format to Traditional
Visit Your Group | Yahoo! Groups Terms of Use | Unsubscribe
Tidak ada komentar:
Posting Komentar