15 Messages
Digest #3060
Messages
Tue May 6, 2014 8:29 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
The formulas are non-volatile by design, so they should only be
recalculating if you do things that causes EXCEL to recalculate them. For
example, using a volatile parameter in the function. Or deleting rows or
columns. And, of course, the smfForceRecalculation macro.
On Tue, May 6, 2014 at 8:13 AM, <cyankee65@gmail.com> wrote:
>
> Knowing me, I missed it in the documentation, is it possible to restrict
> how often data is received using (mainly I use the Yahoo history and Yahoo
> quotes function) time or some other outside value without losing what has
> already been received?
>
> Example would be that data is pulled when you open the worksheet, then no
> matter how often you make changes to the worksheet, nothing changes until
> say top of the hour, or maybe a flag is change from a false status to a
> true status.
>
> I am pulling data on 30 stocks in one worksheet and it is really bogging
> down on me.
>
recalculating if you do things that causes EXCEL to recalculate them. For
example, using a volatile parameter in the function. Or deleting rows or
columns. And, of course, the smfForceRecalculation macro.
On Tue, May 6, 2014 at 8:13 AM, <cyankee65@gmail.com> wrote:
>
> Knowing me, I missed it in the documentation, is it possible to restrict
> how often data is received using (mainly I use the Yahoo history and Yahoo
> quotes function) time or some other outside value without losing what has
> already been received?
>
> Example would be that data is pulled when you open the worksheet, then no
> matter how often you make changes to the worksheet, nothing changes until
> say top of the hour, or maybe a flag is change from a false status to a
> true status.
>
> I am pulling data on 30 stocks in one worksheet and it is really bogging
> down on me.
>
Tue May 6, 2014 12:09 pm (PDT) . Posted by:
confederate_yankee1965
That's the problem, each time I change a value in the spreadsheet, say looking at one group of stocks as opposed to another, the program bogs down. I could change Excel itself to only calculate when I press the F9 key, but then I have to change it back for different spreadsheets.
Tue May 6, 2014 12:39 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
If it does this because you used the NOW() function in the
RCHGetYahooQuotes() function, that is what NOW() does. It makes the
function volatile so that it recalculates with every change. Otherwise,
pressing F9 would do nothing to recalculate RCHGetYahooQuotes() functions.
If that's not the issue, there must be some other way you've made the
functions volatile...?
On Tue, May 6, 2014 at 12:09 PM, <cyankee65@gmail.com> wrote:
>
> That's the problem, each time I change a value in the spreadsheet, say
> looking at one group of stocks as opposed to another, the program bogs
> down. I could change Excel itself to only calculate when I press the F9
> key, but then I have to change it back for different spreadsheets.
>
RCHGetYahooQuotes() function, that is what NOW() does. It makes the
function volatile so that it recalculates with every change. Otherwise,
pressing F9 would do nothing to recalculate RCHGetYahooQuotes() functions.
If that's not the issue, there must be some other way you've made the
functions volatile...?
On Tue, May 6, 2014 at 12:09 PM, <cyankee65@gmail.com> wrote:
>
> That's the problem, each time I change a value in the spreadsheet, say
> looking at one group of stocks as opposed to another, the program bogs
> down. I could change Excel itself to only calculate when I press the F9
> key, but then I have to change it back for different spreadsheets.
>
Tue May 6, 2014 11:57 am (PDT) . Posted by:
jharlan
Randy, have an example of how to "array-enter over a range?" thx,
Tue May 6, 2014 12:37 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
See the documentation and example it refers to...
On Tue, May 6, 2014 at 11:57 AM, <jharlan@yahoo.com> wrote:
>
> Randy, have an example of how to "array-enter over a range?" thx,
>
On Tue, May 6, 2014 at 11:57 AM, <jharlan@yahoo.com> wrote:
>
> Randy, have an example of how to "array-
>
Tue May 6, 2014 11:38 pm (PDT) . Posted by:
shah_ankush
Hi Randy, thanks a lot for your helpful reply! I will try this...
Wed May 7, 2014 7:07 am (PDT) . Posted by:
shah_ankush
Hi Randy, Just to let you know that I tried it and it works like a dream now :-)
Thanks again!
Thanks again!
Tue May 6, 2014 1:34 pm (PDT) . Posted by:
panda317
yeah, I did. It tells me to look for examples in templates that don't exist.
Tue May 6, 2014 1:59 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
I'll need more details...
On Tue, May 6, 2014 at 1:34 PM, <pat@patrosenheim.com> wrote:
>
> yeah, I did. It tells me to look for examples in templates that don't
> exist.
>
On Tue, May 6, 2014 at 1:34 PM, <pat@patrosenheim.com> wrote:
>
> yeah, I did. It tells me to look for examples in templates that don't
> exist.
>
Tue May 6, 2014 2:16 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Sorry:
SMF-Template-Yahoo-Key-Statistics.xls
...is now:
RCHGetElementNumber-Template-Yahoo-Key-Statistics.xls
On Tue, May 6, 2014 at 1:34 PM, <pat@patrosenheim.com> wrote:
>
> yeah, I did. It tells me to look for examples in templates that don't
> exist.
>
SMF-Template-Yahoo-Key-Statistics.xls
...is now:
RCHGetElementNumber-Template-Yahoo-Key-Statistics.xls
On Tue, May 6, 2014 at 1:34 PM, <pat@patrosenheim.com> wrote:
>
> yeah, I did. It tells me to look for examples in templates that don't
> exist.
>
Tue May 6, 2014 4:00 pm (PDT) . Posted by:
"Pat Rosenheim" panda317
OK, currently I have;
=RCHGetTableCell("http://finviz.com/quote.ashx?t="&smfJoin($D$2:$D30,","),1,"?t="&$D30&"&",">Dividend
%")
When I replace that with;
='C:\SMF
Add-in\RCH_Stock_Market_Functions.xla'!RCHGetElementNumber(Ticker,992)
I get ##### and #NAME and that's not at all what I was looking for.
I'm really pretty stupid, I guess.
On 5/6/2014 5:16 PM, Randy Harmelink wrote:
> Sorry:
>
> SMF-Template-Yahoo-Key-Statistics.xls
>
> ...is now:
>
> RCHGetElementNumber-Template-Yahoo-Key-Statistics.xls
>
>
> On Tue, May 6, 2014 at 1:34 PM, <pat@patrosenheim.com
> <mailto:pat@patrosenheim.com>> wrote:
>
>
> yeah, I did. It tells me to look for examples in templates that
> don't exist.
>
>
>
--
Pat Rosenheim (PandA Trader) High Yield, High Return Dividend Portfolio
pat@patrosenheim.com <mailto:pat.rosenheim@gmail.com>
http://www.patrosenheim.com
=RCHGetTableCell("http://finviz.com/quote.ashx?t="&smfJoin($D$2:$D30,","),1,"?t="&$D30&"&",">Dividend
%")
When I replace that with;
='C:\SMF
Add-in\RCH_Stock_Market_Functions.xla'!RCHGetElementNumber(Ticker,992)
I get ##### and #NAME and that's not at all what I was looking for.
I'm really pretty stupid, I guess.
On 5/6/2014 5:16 PM, Randy Harmelink wrote:
> Sorry:
>
> SMF-Template-Yahoo-Key-Statistics.xls
>
> ...is now:
>
> RCHGetElementNumber-Template-Yahoo-Key-Statistics.xls
>
>
> On Tue, May 6, 2014 at 1:34 PM, <pat@patrosenheim.com
> <mailto:pat@patrosenheim.com>> wrote:
>
>
> yeah, I did. It tells me to look for examples in templates that
> don't exist.
>
>
>
--
Pat Rosenheim (PandA Trader) High Yield, High Return Dividend Portfolio
pat@patrosenheim.com <mailto:pat.rosenheim@gmail.com>
http://www.patrosenheim.com
Tue May 6, 2014 4:29 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
It's just a matter of getting used to something new. Always a learning
curve.
Any time you see a location prefix like:
'C:\SMF Add-in\RCH_Stock_Market_Functions.xla'!
...it needs to be removed. That's something EXCEL does when you open up a
workbook that had the add-in in a different location than the person that
saved the workbook. You should have gotten a warning message from EXCEL
about "unresolved links". You can either manually do a "Find and Replace
All" to remove all of them or just run the smfFixLinks macro (I have this
saved to a button my my Quick Access Toolbar).
What you need is something like:
=RCHGetElementNumber(D2,992)
...and then copy it down the row. HOWEVER! It can be MUCH slower than
FinViz, because now you need to do an Internet access to get a web page for
each ticker symbol, while FinViz was able to get the whole list with a
single Internet access.
On Tue, May 6, 2014 at 4:00 PM, Pat Rosenheim <pat@patrosenheim.com> wrote:
> OK, currently I have;
>
> =RCHGetTableCell("http://finviz.com/quote.ashx?t="<http://finviz.com/quote.ashx?t=>&smfJoin($D$2:$D30,","),1,"?t="&$D30&"&",">Dividend
> %")
>
> When I replace that with;
>
> ='C:\SMF
> Add-in\RCH_Stock_Market_Functions.xla'!RCHGetElementNumber(Ticker,992)
>
> I get ##### and #NAME and that's not at all what I was looking for.
>
> I'm really pretty stupid, I guess.
>
curve.
Any time you see a location prefix like:
'C:\SMF Add-in\RCH_Stock_Market_Functions.xla'!
...it needs to be removed. That's something EXCEL does when you open up a
workbook that had the add-in in a different location than the person that
saved the workbook. You should have gotten a warning message from EXCEL
about "unresolved links". You can either manually do a "Find and Replace
All" to remove all of them or just run the smfFixLinks macro (I have this
saved to a button my my Quick Access Toolbar).
What you need is something like:
=RCHGetElementNumber(D2,992)
...and then copy it down the row. HOWEVER! It can be MUCH slower than
FinViz, because now you need to do an Internet access to get a web page for
each ticker symbol, while FinViz was able to get the whole list with a
single Internet access.
On Tue, May 6, 2014 at 4:00 PM, Pat Rosenheim <pat@patrosenheim.com> wrote:
> OK, currently I have;
>
> =RCHGetTableCell("http://finviz.com/quote.ashx?t="<http://finviz.com/quote.ashx?t=>&smfJoin(
> %")
>
> When I replace that with;
>
> ='C:\
> Add-in\RCH_
>
> I get ##### and #NAME and that's not at all what I was looking for.
>
> I'm really pretty stupid, I guess.
>
Tue May 6, 2014 8:28 pm (PDT) . Posted by:
"Pat Rosenheim" panda317
Funny you should say that, because finviz.com has been down for a couple
of hours now....
....my spreadsheet is hung up.
On 5/6/2014 7:29 PM, Randy Harmelink wrote:
> It's just a matter of getting used to something new. Always a learning
> curve.
>
> Any time you see a location prefix like:
>
> 'C:\SMF Add-in\RCH_Stock_Market_Functions.xla'!
>
> ...it needs to be removed. That's something EXCEL does when you open
> up a workbook that had the add-in in a different location than the
> person that saved the workbook. You should have gotten a warning
> message from EXCEL about "unresolved links". You can either manually
> do a "Find and Replace All" to remove all of them or just run the
> smfFixLinks macro (I have this saved to a button my my Quick Access
> Toolbar).
>
> What you need is something like:
>
> =RCHGetElementNumber(D2,992)
>
> ...and then copy it down the row. HOWEVER! It can be MUCH slower than
> FinViz, because now you need to do an Internet access to get a web
> page for each ticker symbol, while FinViz was able to get the whole
> list with a single Internet access.
>
> On Tue, May 6, 2014 at 4:00 PM, Pat Rosenheim <pat@patrosenheim.com
> <mailto:pat@patrosenheim.com>> wrote:
>
> OK, currently I have;
>
> =RCHGetTableCell("http://finviz.com/quote.ashx?t="
> <http://finviz.com/quote.ashx?t=>&smfJoin($D$2:$D30,","),1,"?t="&$D30&"&",">Dividend
> %")
>
> When I replace that with;
>
> ='C:\SMF
> Add-in\RCH_Stock_Market_Functions.xla'!RCHGetElementNumber(Ticker,992)
>
> I get ##### and #NAME and that's not at all what I was looking for.
>
> I'm really pretty stupid, I guess.
>
>
>
--
Pat Rosenheim (PandA Trader) High Yield, High Return Dividend Portfolio
pat@patrosenheim.com <mailto:pat.rosenheim@gmail.com>
http://www.patrosenheim.com
of hours now....
....my spreadsheet is hung up.
On 5/6/2014 7:29 PM, Randy Harmelink wrote:
> It's just a matter of getting used to something new. Always a learning
> curve.
>
> Any time you see a location prefix like:
>
> 'C:\SMF Add-in\RCH_Stock_Market_Functions.xla'!
>
> ...it needs to be removed. That's something EXCEL does when you open
> up a workbook that had the add-in in a different location than the
> person that saved the workbook. You should have gotten a warning
> message from EXCEL about "unresolved links". You can either manually
> do a "Find and Replace All" to remove all of them or just run the
> smfFixLinks macro (I have this saved to a button my my Quick Access
> Toolbar).
>
> What you need is something like:
>
> =RCHGetElementNumber(D2,992)
>
> ...and then copy it down the row. HOWEVER! It can be MUCH slower than
> FinViz, because now you need to do an Internet access to get a web
> page for each ticker symbol, while FinViz was able to get the whole
> list with a single Internet access.
>
> On Tue, May 6, 2014 at 4:00 PM, Pat Rosenheim <pat@patrosenheim.com
> <mailto:pat@patrosenheim.com>> wrote:
>
> OK, currently I have;
>
> =RCHGetTableCell("http://finviz.com/quote.ashx?t="
> <http://finviz.com/quote.ashx?t=>&smfJoin($D$2:$D30,","),1,"?t="&$D30&"&",">Dividend
> %")
>
> When I replace that with;
>
> ='C:\SMF
> Add-in\RCH_Stock_Market_Functions.xla'!RCHGetElementNumber(Ticker,992)
>
> I get ##### and #NAME and that's not at all what I was looking for.
>
> I'm really pretty stupid, I guess.
>
>
>
--
Pat Rosenheim (PandA Trader) High Yield, High Return Dividend Portfolio
pat@patrosenheim.com <mailto:pat.rosenheim@gmail.com>
http://www.patrosenheim.com
Wed May 7, 2014 7:19 am (PDT) . Posted by:
"Pat Rosenheim" panda317
And it turns out that yahoo returns ERROR for at least 10 of my stocks
with just this one function;
=RCHGetElementNumber(D2,992)
So now my question is, is there a site that provides reliable information?
On 5/6/2014 7:29 PM, Randy Harmelink wrote:
> It's just a matter of getting used to something new. Always a learning
> curve.
>
> Any time you see a location prefix like:
>
> 'C:\SMF Add-in\RCH_Stock_Market_Functions.xla'!
>
> ...it needs to be removed. That's something EXCEL does when you open
> up a workbook that had the add-in in a different location than the
> person that saved the workbook. You should have gotten a warning
> message from EXCEL about "unresolved links". You can either manually
> do a "Find and Replace All" to remove all of them or just run the
> smfFixLinks macro (I have this saved to a button my my Quick Access
> Toolbar).
>
> What you need is something like:
>
> =RCHGetElementNumber(D2,992)
>
> ...and then copy it down the row. HOWEVER! It can be MUCH slower than
> FinViz, because now you need to do an Internet access to get a web
> page for each ticker symbol, while FinViz was able to get the whole
> list with a single Internet access.
>
> On Tue, May 6, 2014 at 4:00 PM, Pat Rosenheim <pat@patrosenheim.com
> <mailto:pat@patrosenheim.com>> wrote:
>
> OK, currently I have;
>
> =RCHGetTableCell("http://finviz.com/quote.ashx?t="
> <http://finviz.com/quote.ashx?t=>&smfJoin($D$2:$D30,","),1,"?t="&$D30&"&",">Dividend
> %")
>
> When I replace that with;
>
> ='C:\SMF
> Add-in\RCH_Stock_Market_Functions.xla'!RCHGetElementNumber(Ticker,992)
>
> I get ##### and #NAME and that's not at all what I was looking for.
>
> I'm really pretty stupid, I guess.
>
>
>
--
Pat Rosenheim (PandA Trader) High Yield, High Return Dividend Portfolio
pat@patrosenheim.com <mailto:pat.rosenheim@gmail.com>
http://www.patrosenheim.com
with just this one function;
=RCHGetElementNumber(D2,992)
So now my question is, is there a site that provides reliable information?
On 5/6/2014 7:29 PM, Randy Harmelink wrote:
> It's just a matter of getting used to something new. Always a learning
> curve.
>
> Any time you see a location prefix like:
>
> 'C:\SMF Add-in\RCH_Stock_Market_Functions.xla'!
>
> ...it needs to be removed. That's something EXCEL does when you open
> up a workbook that had the add-in in a different location than the
> person that saved the workbook. You should have gotten a warning
> message from EXCEL about "unresolved links". You can either manually
> do a "Find and Replace All" to remove all of them or just run the
> smfFixLinks macro (I have this saved to a button my my Quick Access
> Toolbar).
>
> What you need is something like:
>
> =RCHGetElementNumber(D2,992)
>
> ...and then copy it down the row. HOWEVER! It can be MUCH slower than
> FinViz, because now you need to do an Internet access to get a web
> page for each ticker symbol, while FinViz was able to get the whole
> list with a single Internet access.
>
> On Tue, May 6, 2014 at 4:00 PM, Pat Rosenheim <pat@patrosenheim.com
> <mailto:pat@patrosenheim.com>> wrote:
>
> OK, currently I have;
>
> =RCHGetTableCell("http://finviz.com/quote.ashx?t="
> <http://finviz.com/quote.ashx?t=>&smfJoin($D$2:$D30,","),1,"?t="&$D30&"&",">Dividend
> %")
>
> When I replace that with;
>
> ='C:\SMF
> Add-in\RCH_Stock_Market_Functions.xla'!RCHGetElementNumber(Ticker,992)
>
> I get ##### and #NAME and that's not at all what I was looking for.
>
> I'm really pretty stupid, I guess.
>
>
>
--
Pat Rosenheim (PandA Trader) High Yield, High Return Dividend Portfolio
pat@patrosenheim.com <mailto:pat.rosenheim@gmail.com>
http://www.patrosenheim.com
Wed May 7, 2014 7:48 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
What source do you normally use?
Were the error items stocks, or ETFs? That element number won't work for
ETFs. They don't have Key Statistics web pages.
On Wed, May 7, 2014 at 7:19 AM, Pat Rosenheim <pat@patrosenheim.com> wrote:
> And it turns out that yahoo returns ERROR for at least 10 of my stocks
> with just this one function;
> =RCHGetElementNumber(D2,992)
>
> So now my question is, is there a site that provides reliable information?
>
Were the error items stocks, or ETFs? That element number won't work for
ETFs. They don't have Key Statistics web pages.
On Wed, May 7, 2014 at 7:19 AM, Pat Rosenheim <pat@patrosenheim.com> wrote:
> And it turns out that yahoo returns ERROR for at least 10 of my stocks
> with just this one function;
> =RCHGetElementNumbe
>
> So now my question is, is there a site that provides reliable information?
>
Tidak ada komentar:
Posting Komentar