Jumat, 07 Februari 2014

[smf_addin] Digest Number 2958[5 Attachments]

12 New Messages

Digest #2958
1a
Re: Problem with RCHGetYahooQuotes function by "Randy Harmelink" rharmelink
4b
Re: Array range for RCHgetelement number by "Randy Harmelink" rharmelink
4c
Re: Array range for RCHgetelement number by "Sill. Micheal" futants46and2

Messages

Thu Feb 6, 2014 12:54 am (PST) . Posted by:

"Randy Harmelink" rharmelink

What I would do is retrieve the data with RCHGetYahooQuotes() in columns
A-C (or in a separate worksheet), and then create your presentation of the
data starting in column E. You can refer to the data in columns A-C as
needed in the presentation.

Then you can delete ranges from your presentation area, or sort your
presentation area, easily. Whenever you do, columns A-C will recalculate
automatically based on how the ticker symbol column has changed.

On Wed, Feb 5, 2014 at 11:58 PM, <bameyer@optonline.net> wrote:

> Yes, now it is after midnight and the data is coming up faster but it
> can take a long time. I did set up the array in the test workbook for the
> Name and Current Price. I couldn't get the array to work for the Yield.
> It is much faster. I do sell stocks and buy other ones from time to time
> and I can't just delete the lines of the deleted stocks and insert the new
> ones into the array. Do I just have to break the array and set it up again
> each time I make a change? I am running Excel 2003 so the other formula is
> not an option.
>

Thu Feb 6, 2014 6:26 am (PST) . Posted by:

"Kermit W. Prather" kermitpra

I just downloaded the spreadsheet. change the links to fit my placement of the SMF-ADDIN. I ran in less than a 30 seconds.

Kermit

From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf Of bameyer@optonline.net
Sent: Wednesday, February 05, 2014 5:57 PM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] Problem with RCHGetYahooQuotes function [1 Attachment]

[Attachment(s) from bameyer@optonline.net included below]

I have a test spreadsheet with 11 stocks in it. Each line looks up the stock name, current price, and the yield. These 33 lookups are taking about 5 minutes to complete. My actual spreadsheet will have 85 stocks in it. Is there a better way to use the function so that it responds quickly? It used to run almost instantaneously on this test spreadsheet.

Bruce

Thu Feb 6, 2014 10:37 am (PST) . Posted by:

futants46and2

Hey Randy,
Ive been running a version of SMF add-in for a few years now, always worked great. The past few days its been taking extremely long to update all tickers + info, today it stopped loading new data requests and crashing excel.
I am running RCH stock market function 2.2010.08.02 . Do I need to upgrade? Ive noticed some emails going around about an update you've done for the add-in.

Thanks.
Elliot.

Thu Feb 6, 2014 10:56 am (PST) . Posted by:

"Randy Harmelink" rharmelink

If you're using a large number of individual RCHGetYahooQuotes(), instead
of array-entering them over ranges, that could be the problem. There have
been a lot of complaints lately about how slow (and unreliable) Yahoo has
been getting. An upgrade won't fix that, but I still would suggest doing it.

But array-entering RCHGetYahooQuotes() over a range could significantly
reduce the number of Internet accesses you need to do, reducing the
possibility of a time-out on one or more requests (which could make it look
like EXCEL has stopped responding).

Are you physically getting a crash message, or are you assuming a crash
because it's not responding?

On Thu, Feb 6, 2014 at 11:37 AM, <futants46and2@yahoo.com> wrote:

>
> Ive been running a version of SMF add-in for a few years now, always
> worked great. The past few days its been taking extremely long to update
> all tickers + info, today it stopped loading new data requests and crashing
> excel.
> I am running RCH stock market function 2.2010.08.02 . Do I need to
> upgrade? Ive noticed some emails going around about an update you've done
> for the add-in.
>

Thu Feb 6, 2014 11:10 am (PST) . Posted by:

"Sill. Micheal" futants46and2

Excel usually loads in spurts up till about 50% and stops responding. I usually wait a few minutes and shut it down when it is clear it has stopped responding completely.

On Thursday, February 6, 2014 1:56 PM, Randy Harmelink <rharmelink@gmail.com> wrote:

 
If you're using a large number of individual RCHGetYahooQuotes(), instead of array-entering them over ranges, that could be the problem. There have been a lot of complaints lately about how slow (and unreliable) Yahoo has been getting. An upgrade won't fix that, but I still would suggest doing it.

But array-entering RCHGetYahooQuotes() over a range could significantly reduce the number of Internet accesses you need to do, reducing the possibility of a time-out on one or more requests (which could make it look like EXCEL has stopped responding).

Are you physically getting a crash message, or are you assuming a crash because it's not responding?

On Thu, Feb 6, 2014 at 11:37 AM, <futants46and2@yahoo.com> wrote:

>Ive been running a version of SMF add-in for a few years now, always worked great. The past few days its been taking extremely long to update all tickers + info, today it stopped loading new data requests and crashing excel.
>I am running RCH stock market function 2.2010.08.02 . Do I need to upgrade? Ive noticed some emails going around about an update you've done for the add-in.
>

Thu Feb 6, 2014 11:32 am (PST) . Posted by:

"Sill. Micheal" futants46and2

Hi Randy,

I ran a test & deleted all tickers except for 1 ticker. When I reopened the sheet it loaded all requests for said ticker in normal time. So I guess the old SMF is not the prob. I will try array entering the SMF formula as ranges for all ticker requests.

I am not sure how to add a range array with one formula for each column all the way down, Do you have any documentation on arrays & the SMF plugin,I have not seen any in thedoc.
area.

Thanks,
Elliot.

On Thursday, February 6, 2014 1:58 PM, Randy Harmelink <rharmelink@gmail.com> wrote:

 
If you're using a large number of individual RCHGetYahooQuotes(), instead of array-entering them over ranges, that could be the problem. There have been a lot of complaints lately about how slow (and unreliable) Yahoo has been getting. An upgrade won't fix that, but I still would suggest doing it.

But array-entering RCHGetYahooQuotes() over a range could significantly reduce the number of Internet accesses you need to do, reducing the possibility of a time-out on one or more requests (which could make it look like EXCEL has stopped responding).

Are you physically getting a crash message, or are you assuming a crash because it's not responding?

On Thu, Feb 6, 2014 at 11:37 AM, <futants46and2@yahoo.com> wrote:

>Ive been running a version of SMF add-in for a few years now, always worked great. The past few days its been taking extremely long to update all tickers + info, today it stopped loading new data requests and crashing excel.
>I am running RCH stock market function 2.2010.08.02 . Do I need to upgrade? Ive noticed some emails going around about an update you've done for the add-in.
>

Thu Feb 6, 2014 11:49 am (PST) . Posted by:

"Sill. Micheal" futants46and2

Nevermind I found the section on arrays.

Thanks,
Elliot.

On Thursday, February 6, 2014 2:33 PM, Sill. Micheal <futants46and2@yahoo.com> wrote:

 
Hi Randy,

I ran a test & deleted all tickers except for 1 ticker. When I reopened the sheet it loaded all requests for said ticker in normal time. So I guess the old SMF is not the prob. I will try array entering the SMF formula as ranges for all ticker requests.

I am not sure how to add a range array with one formula for each column all the way down, Do you have any documentation on arrays & the SMF plugin,I have not seen any in thedoc.
area.

Thanks,
Elliot.

On Thursday, February 6, 2014 1:58 PM, Randy Harmelink <rharmelink@gmail.com> wrote:

 
If you're using a large number of individual RCHGetYahooQuotes(), instead of array-entering them over ranges, that could be the problem. There have been a lot of complaints lately about how slow (and unreliable) Yahoo has been getting. An upgrade won't fix that, but I still would suggest doing it.

But array-entering RCHGetYahooQuotes() over a range could significantly reduce the number of Internet accesses you need to do, reducing the possibility of a time-out on one or more requests (which could make it look like EXCEL has stopped responding).

Are you physically getting a crash message, or are you assuming a crash because it's not responding?

On Thu, Feb 6, 2014 at 11:37 AM, <futants46and2@yahoo.com> wrote:

>Ive been running a version of SMF add-in for a few years now, always worked great. The past few days its been taking extremely long to update all tickers + info, today it stopped loading new data requests and crashing excel.
>I am running RCH stock market function 2.2010.08.02 . Do I need to upgrade? Ive noticed some emails going around about an update you've done for the add-in.
>

Thu Feb 6, 2014 1:55 pm (PST) . Posted by:

futants46and2

Hi Randy,

I am trying to figure out the formula to set an array range for
4 columns, each a different RCH get element number across.
100 rows of tickers down.

Thanks,
Elliot.

Thu Feb 6, 2014 2:10 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

RCHGetElementNumber() can't be array-entered over a range. There would be
no advantage to it, since the number of web page retrievals would not
change. You would need to use 400 individual formulas, although you can
create one and then copy it over the range.

Sounds like you would need to access anywhere from 100 to 400 web pages.
Could take a while.

You may want to look at the smfUpdateDownloadTable macro (see LINKS area of
group for more info). It has the advantage of filling the 400 cells with
values, instead of formulas that need to recalculate every time you open
the workbook. But you can always run the macro again to update all the
values. But no real speed difference in the actual retrieval process.

Another option is to see if FinViz has the data you need. All of its data
items can be retrieved on a number of different ticker symbols, in a single
Internet access. Again, see the LINKS area of the group for more info.

On Thu, Feb 6, 2014 at 2:55 PM, <futants46and2@yahoo.com> wrote:

>
> I am trying to figure out the formula to set an array range for
> 4 columns, each a different RCH get element number across.
> 100 rows of tickers down.
>

Thu Feb 6, 2014 2:17 pm (PST) . Posted by:

"Sill. Micheal" futants46and2

I see, that makes sense I tried hundreds of diff arrays but quickly realized its the same problem of massive multiple requests. I will check links for smfUpdateDownloadTable macro and finviz.

Thanks

On Thursday, February 6, 2014 5:11 PM, Randy Harmelink <rharmelink@gmail.com> wrote:

 
RCHGetElementNumber() can't be array-entered over a range. There would be no advantage to it, since the number of web page retrievals would not change. You would need to use 400 individual formulas, although you can create one and then copy it over the range.

Sounds like you would need to access anywhere from 100 to 400 web pages. Could take a while.

You may want to look at the smfUpdateDownloadTable macro (see LINKS area of group for more info). It has the advantage of filling the 400 cells with values, instead of formulas that need to recalculate every time you open the workbook. But you can always run the macro again to update all the values. But no real speed difference in the actual retrieval process.

Another option is to see if FinViz has the data you need. All of its data items can be retrieved on a number of different ticker symbols, in a single Internet access. Again, see the LINKS area of the group for more info.

On Thu, Feb 6, 2014 at 2:55 PM, <futants46and2@yahoo.com> wrote:

>I am trying to figure out the formula to set an array range for
>4 columns, each a different RCH get element number across.
>100 rows of tickers down.
>

Thu Feb 6, 2014 8:41 pm (PST) . Posted by:

"babond" babond2

I am still trying to get the file :

"SMF-Template-10-Year2-Charts"

to function in my excel 2007, using Internet Explorer, computer HP dv9830 Notebook.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
With SMF Add-in loaded in C:drive => C:\SMF Add-In\RCH_Stock_Market_Functions-2.1.2012.12.29

I first get the alert message:
'C:\Program Files\SMF Add-In\RCH_Stock .xla' could not be found.Check the spelling of the file name, and verify that the file location is correct. If you are trying to open the file from you list of most recently used files, make sure that the file has not been renamed, moved, or deleted.'
when I open the file. But, clicking 'OK' opens the file anyway, to the saved ticker "IBM" symbol, and displays the charts. (see screen shots 1 and 2)

however, I cannot change the ticker symbol. Everything goes blank on the chart, with many errors in the "data sheet" (see screen shots 3 and 4)

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

With SMF Add-in loaded in C:drive subdirectory => C:\Program Files\SMF Add-In\RCH_Stock_Market_Functions-2.1.2012.12.29

I first get the same alert message:
'C:\Program Files\SMF Add-In\RCH_Stock .xla' could not be found.Check the spelling of the file name, and verify that the file location is correct. If you are trying to open the file from you list of most recently used files, make sure that the file has not been renamed, moved, or deleted.'

again by clicking 'OK' opens the file anyway, to the saved ticker "IBM" symbol, and displays the charts. (see screen shots 1 and 2)

however I still cannot change the ticker symbol. Everything goes blank on the chart, with many errors in the "data sheet" (see screen shots 3 and 4)

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

screen shot 5 shows that the SMF Add-in is installed and active, in both cases.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

As a check for the formulas, on the Data sheet: on the default (downloaded) file.

Cell D2 reads => ='C:\Program Files\SMF Add-In\RCH_Stock_Market_Functions.xla'!RCHGetElementNumber(Ticker,8006)

Cell D4 reads => ='C:\Program Files\SMF Add-In\RCH_Stock_Market_Functions.xla'!RCHGetElementNumber(Ticker,11326)-&#39;C:\Program Files\SMF Add-In\RCH_Stock_Market_Functions.xla'!RCHGetElementNumber(Ticker,11330)+E4

On the changed Ticker (in this case) Ticker = GE
Cell D2 reads => #NAME?
Cell D4 reads => #NAME?
and almost all the Cells are the same (see screen shot (4)).

I sure must have something wrong, but I cannot understand exactly what is going on.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

BAB

----- Original Message -----
From: babond@suddenlink.net
To: smf_addin@yahoogroups.com
Sent: Thursday, January 16, 2014 12:11 AM
Subject: [smf_addin] SMF-Template-10-Year-Charts.xls

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.

Attachments with this message:
5 of 5 Photo(s)

Thu Feb 6, 2014 9:31 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

See the entry on "Location errors" from the LINKS area of the group...

I'm also confused why your install directory folder has the version name of
the add-in in it. You did extract all the files, right? You're not
referencing the ZIP file directly?

For myself, I open the workbook in question from the FILES area of the
group, tell EXCEL to "Enable the Content" when I get the error message
about unresolved links, then click on "Continue";, and then run the
smfFixLinks macro. And everything is fine.

What do you get with:

=RCHGetElementNumber("Version&quot;)

On Thu, Feb 6, 2014 at 9:40 PM, babond <babond@suddenlink.net> wrote:

> 
> I am still trying to get the file :
>
> "SMF-Template-10-Year2-Charts"
>
> to function in my excel 2007, using Internet Explorer, computer HP dv9830
> Notebook.
>
> - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> With SMF Add-in loaded in C:drive => C:\SMF
> Add-In\RCH_Stock_Market_Functions-2.1.2012.12.29
>
> I first get the alert message:
> *'C:\Program Files\SMF Add-In\RCH_Stock .xla' could not be found.Check the
> spelling of the file name, and verify that the file location is correct.
> If you are trying to open the file from you list of most recently used
> files, make sure that the file has not been renamed, moved, or deleted.*'
> when I open the file. But, clicking 'OK' opens the file anyway, to the
> saved ticker "IBM" symbol, and displays the charts. (see screen shots 1
> and 2)
>
> however, I cannot change the ticker symbol. Everything goes blank on the
> chart, with many errors in the "data sheet" (see screen shots 3 and 4)
>
> - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
>
> With SMF Add-in loaded in C:drive subdirectory => C:\Program Files\SMF
> Add-In\RCH_Stock_Market_Functions-2.1.2012.12.29
>
> I first get the same alert message:
> *'C:\Program Files\SMF Add-In\RCH_Stock .xla' could not be found.Check the
> spelling of the file name, and verify that the file location is correct.
> If you are trying to open the file from you list of most recently used
> files, make sure that the file has not been renamed, moved, or deleted.'*
>
> again by clicking 'OK' opens the file anyway, to the saved ticker "IBM"
> symbol, and displays the charts. (see screen shots 1 and 2)
>
> however I still cannot change the ticker symbol. Everything goes blank on
> the chart, with many errors in the "data sheet" (see screen shots 3 and 4)
>
> - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
>
> screen shot 5 shows that the SMF Add-in is installed and active, in both
> cases.
>
>
> - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
>
> As a check for the formulas, on the Data sheet: on the default
> (downloaded) file.
>
> Cell D2 reads => ='C:\Program Files\SMF
> Add-In\RCH_Stock_Market_Functions.xla'!RCHGetElementNumber(Ticker,8006)
>
> Cell D4 reads => ='C:\Program Files\SMF
> Add-In\RCH_Stock_Market_Functions.xla'!RCHGetElementNumber(Ticker,11326)-&#39;C:\Program
> Files\SMF
> Add-In\RCH_Stock_Market_Functions.xla'!RCHGetElementNumber(Ticker,11330)+E4
>
> On the changed Ticker (in this case) Ticker = GE
> Cell D2 reads => #NAME?
> Cell D4 reads => #NAME?
> and almost all the Cells are the same (see screen shot (4)).
>
>
> I sure must have something wrong, but I cannot understand exactly what is
> going on.
> +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> BAB
>
Diposting oleh EXCEL FEED LEARNING di 00.42

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)