3 Messages
Digest #3129
Messages
Wed Jul 23, 2014 6:32 am (PDT) . Posted by:
fredwwright
What I do for the Schwab Equity Ratings (and several other ratings on the Schwab site - SP stars, Ned Davis etc) is export them into an Excell file and then use vLookup to extract what I want into a spreadsheet with other SMF pulled data. The only problems with this approach are: 1) you can only export 1000 at a time but this can be overcome by grouping into each schwab rating; and 2) they have split their international ratings into a separate group which means that all the ADRs are only available on that side and thus don't have the SP Stars or Ned Davis ratings. Once you download the information the Vlookup is very fast.
Fred
Fred
Wed Jul 23, 2014 10:30 am (PDT) . Posted by:
weldenc
I use the RCH_Stock_Market_Functions.xla in a spreadsheet in Excel 2007. I have attached a screenshot of the main worksheet that the rest of the spreadsheet feeds off. I believe that is was originally a template I downloaded from here several years ago.
Yesterday after many years of fairly reliable use, I could not access any quote info. I tried relinking to the add-in but nothing would work. So I thought I would download a newer copy of RCH_Stock_Market_Functions.xla in case it had changed. (My previous copy is probably 5 years old). I made a back up copy of the old one and installed the new one in the same location (C:\Program Files\SMF Add-in\).
At that point, I noticed a message in the main worksheet that something to the effect that Yahoo limited stock quotes to 200 stocks. My worksheet had 202. I figured out how to delete the extra 2 lines. ( I wasn't using them). Everything started working again. (I thought)
This morning as I started work, I noticed that the stock quotes were not updating. They normally update whenever I make a change to any worksheet. I can also use the Calculate Now button or F9 to make them update. However, I noticed that they didn't change when I made changes or used the F9 key.
I went to the "Data" tab on the ribbon and clicked on "Edit links". It showed RCH_Stock_Market_Functions.xla status was "Unknown". If I do a "Check Source", it says it is open. If I do "Open Source", I get an Excel Security Notice about macros. When I pick "Enable Macros" all the stock quotes get updated. If I go back to "Edit Links" it shows source unknown for RCH_Stock_Market_Functions.xla again.
I suspect part of my problem might be the main worksheet where I deleted 2 lines to get rid of the "Exceeding 200 stocks" message. Those 2 lines were at the bottom of the sheet near a cell that contains a RAND() function. It seems that I recall that it might have been used in initiating the refresh of the quotes.
If any can shed some light on my problem, I would be grateful.
Yesterday after many years of fairly reliable use, I could not access any quote info. I tried relinking to the add-in but nothing would work. So I thought I would download a newer copy of RCH_Stock_Market_
At that point, I noticed a message in the main worksheet that something to the effect that Yahoo limited stock quotes to 200 stocks. My worksheet had 202. I figured out how to delete the extra 2 lines. ( I wasn't using them). Everything started working again. (I thought)
This morning as I started work, I noticed that the stock quotes were not updating. They normally update whenever I make a change to any worksheet. I can also use the Calculate Now button or F9 to make them update. However, I noticed that they didn't change when I made changes or used the F9 key.
I went to the "Data" tab on the ribbon and clicked on "Edit links". It showed RCH_Stock_Market_
I suspect part of my problem might be the main worksheet where I deleted 2 lines to get rid of the "Exceeding 200 stocks" message. Those 2 lines were at the bottom of the sheet near a cell that contains a RAND() function. It seems that I recall that it might have been used in initiating the refresh of the quotes.
If any can shed some light on my problem, I would be grateful.
Attachment(s) from
1 of 1 Photo(s)
Wed Jul 23, 2014 10:36 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Yeah, the RAND() was what made the function volatile, so that F9 would make
it update. If you check the documentation, you'll find there is now a
function parameter you can use to make the function volatile.
Alternatively, you can use the smfForceRecalculation macro to do your
update.
And, yes, Yahoo has placed a limit of 200 ticker symbols per request.
However, you can use multiple RCHGetYahooQuotes() functions, doing up to
200 ticker symbols per function.
On Wed, Jul 23, 2014 at 10:30 AM, weldencd@comcast.net [smf_addin] <
smf_addin@yahoogroups.com> wrote:
> I use the RCH_Stock_Market_Functions.xla in a spreadsheet in Excel 2007.
> I have attached a screenshot of the main worksheet that the rest of the
> spreadsheet feeds off. I believe that is was originally a template I
> downloaded from here several years ago.
>
> Yesterday after many years of fairly reliable use, I could not access any
> quote info. I tried relinking to the add-in but nothing would work. So I
> thought I would download a newer copy of RCH_Stock_Market_Functions.xla in
> case it had changed. (My previous copy is probably 5 years old). I made a
> back up copy of the old one and installed the new one in the same location
> (C:\Program Files\SMF Add-in\).
>
> At that point, I noticed a message in the main worksheet that something to
> the effect that Yahoo limited stock quotes to 200 stocks. My worksheet had
> 202. I figured out how to delete the extra 2 lines. ( I wasn't using them).
> Everything started working again. (I thought)
>
> This morning as I started work, I noticed that the stock quotes were not
> updating. They normally update whenever I make a change to any worksheet. I
> can also use the Calculate Now button or F9 to make them update. However, I
> noticed that they didn't change when I made changes or used the F9 key.
>
> I went to the "Data" tab on the ribbon and clicked on "Edit links". It
> showed RCH_Stock_Market_Functions.xla status was "Unknown". If I do a
> "Check Source", it says it is open. If I do "Open Source", I get an Excel
> Security Notice about macros. When I pick "Enable Macros" all the stock
> quotes get updated. If I go back to "Edit Links" it shows source unknown
> for RCH_Stock_Market_Functions.xla again.
>
> I suspect part of my problem might be the main worksheet where I deleted 2
> lines to get rid of the "Exceeding 200 stocks" message. Those 2 lines were
> at the bottom of the sheet near a cell that contains a RAND() function. It
> seems that I recall that it might have been used in initiating the refresh
> of the quotes.
>
> If any can shed some light on my problem, I would be grateful.
>
it update. If you check the documentation, you'll find there is now a
function parameter you can use to make the function volatile.
Alternatively, you can use the smfForceRecalculation macro to do your
update.
And, yes, Yahoo has placed a limit of 200 ticker symbols per request.
However, you can use multiple RCHGetYahooQuotes() functions, doing up to
200 ticker symbols per function.
On Wed, Jul 23, 2014 at 10:30 AM, weldencd@comcast.net [smf_addin] <
smf_addin@yahoogroups.com> wrote:
> I use the RCH_Stock_Market_
> I have attached a screenshot of the main worksheet that the rest of the
> spreadsheet feeds off. I believe that is was originally a template I
> downloaded from here several years ago.
>
> Yesterday after many years of fairly reliable use, I could not access any
> quote info. I tried relinking to the add-in but nothing would work. So I
> thought I would download a newer copy of RCH_Stock_Market_
> case it had changed. (My previous copy is probably 5 years old). I made a
> back up copy of the old one and installed the new one in the same location
> (C:\Program Files\SMF Add-in\)
>
> At that point, I noticed a message in the main worksheet that something to
> the effect that Yahoo limited stock quotes to 200 stocks. My worksheet had
> 202. I figured out how to delete the extra 2 lines. ( I wasn't using them).
> Everything started working again. (I thought)
>
> This morning as I started work, I noticed that the stock quotes were not
> updating. They normally update whenever I make a change to any worksheet. I
> can also use the Calculate Now button or F9 to make them update. However, I
> noticed that they didn't change when I made changes or used the F9 key.
>
> I went to the "Data" tab on the ribbon and clicked on "Edit links". It
> showed RCH_Stock_Market_
> "Check Source"
> Security Notice about macros. When I pick "Enable Macros" all the stock
> quotes get updated. If I go back to "Edit Links" it shows source unknown
> for RCH_Stock_Market_
>
> I suspect part of my problem might be the main worksheet where I deleted 2
> lines to get rid of the "Exceeding 200 stocks" message. Those 2 lines were
> at the bottom of the sheet near a cell that contains a RAND() function. It
> seems that I recall that it might have been used in initiating the refresh
> of the quotes.
>
> If any can shed some light on my problem, I would be grateful.
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar