Messages In This Digest (4 Messages)
- 1a.
- Re: Controlling updates From: Ron Spruell
- 1b.
- Re: Controlling updates From: Randy Harmelink
- 2a.
- Re: Get data for over 3000 companies? From: Ron Spruell
- 2b.
- Re: Get data for over 3000 companies? From: Randy Harmelink
Messages
- 1a.
-
Re: Controlling updates
Posted by: "Ron Spruell" hashky@yahoo.com hashky
Sat Oct 8, 2011 8:57 am (PDT)
Randy -
One of the things that I have done is separate the items on the worksheet into 2 worksheets.
For example: I put =rchgetyahooquotes() in an array on a main worksheet. The only other functions I use on that page are =Vlookup() referenced to a slower calculating Worksheeet.
I start the Workbook with a Manual Calculation Macro, and use Shift-F9 to calculate this page only. (I have a toolbar button called Recalculate This Page.)
On another page I put =smfpricesbydates(), =smfGetTagCont ent(), etc. This is my slow page that only needs to be calculated once per day.
Once I run the smfForceRecalculation, then I go to the Main Page and only run that page using Shift-F9.
I have found that this greatly speeds up my calculations. One Workbook went from 12 minutes to about 10 seconds.
Am I missing anything?
Ron Spruell
>____________________ _________ ___
>From: Randy Harmelink <rharmelink@gmail.com >
>To: smf_addin@yahoogroups.com
>Sent: Friday, October 7, 2011 1:05 PM
>Subject: Re: [smf_addin] Controlling updates
>
>
>
>The functions should be non-volatile unless you make them volatile with something like NOW(). If you have things recomputing, it should mean you've made the functions volatile. Also, row/column insertions and deletions will trigger a recalculation.
>
>I leave all of my function invocation non-volatile and then use the smfForceRecalculation macro (assigned to a toolbar button) to update things when I need them updated.
>
>
>On Fri, Oct 7, 2011 at 10:18 AM, Glen Fullmer <gfullmer@cox.net > wrote:
>
>Is there a way not to update any SMF function until a button is pressed?
>>When adding to a spreadsheet it recomputes with every addition. I got
>>rid of the NOW() functions, but it is still slow.
>>
>
>
>
> - 1b.
-
Re: Controlling updates
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Sat Oct 8, 2011 9:20 am (PDT)
I would suspect that you've made your smfPricesByDates() functions volatile
by using something like the DATE() function with them. In cases where I need
the current date in an add-in function, I usually put this in a cell and use
it:
=DateValue(RCHGetYahooQuotes( "SPY","d1" ))
That gives me a non-volatile way of getting the most recent trading date.
On Sat, Oct 8, 2011 at 8:57 AM, Ron Spruell <hashky@yahoo.com > wrote:
>
> One of the things that I have done is separate the items on the worksheet
> into 2 worksheets.
>
> For example: I put =rchgetyahooquotes() in an array on a main worksheet.
> The only other functions I use on that page are =Vlookup() referenced to a
> slower calculating Worksheeet.
> I start the Workbook with a Manual Calculation Macro, and use Shift-F9 to
> calculate this page only. (I have a toolbar button called Recalculate This
> Page.)
>
> On another page I put =smfpricesbydates(), =smfGetTagContent( ), etc. This
> is my slow page that only needs to be calculated once per day.
>
> Once I run the smfForceRecalculation, then I go to the Main Page and only
> run that page using Shift-F9.
>
> I have found that this greatly speeds up my calculations. One Workbook
> went from 12 minutes to about 10 seconds.
>
> Am I missing anything?
>
>
- 2a.
-
Re: Get data for over 3000 companies?
Posted by: "Ron Spruell" hashky@yahoo.com hashky
Sat Oct 8, 2011 9:41 am (PDT)
Randy -
"You can get the 20-day and 200-day SMA from FinViz for all companies they cover in a single Internet access. "
Do you have a sample of this some where on this website? I don't know how to do it.
Ron Spruell
>____________________ _________ ___
>From: Randy Harmelink <rharmelink@gmail.com >
>To: smf_addin@yahoogroups.com
>Sent: Friday, October 7, 2011 1:20 PM
>Subject: Re: [smf_addin] Get data for over 3000 companies?
>
>
>
>The 200 company limit on getting quotes from Yahoo is a Yahoo
limitation, not an add-in limitation. Also, their 200-day SMA is based on CALENDAR days, not TRADING days.
>
>The add-in isn't intended for downloading large amounts of data. I actually don't want people abusing the free data services by doing that type of thing. As it is, the add-in only has storage space for 1000 web pages per "session". As far as I'm concerned, that's more than enough for any reasonable use.
>
>You can get the 20-day and 200-day SMA from FinViz for all companies they cover in a single Internet access. Try using that to "pare down" your 3200 companies to a more reasonable size before going after other data.
>
>
>On Fri, Oct 7, 2011 at 10:29 AM, CheeseMan <jcrenshaw75@yahoo.com > wrote:
>
>
>>
>>I was wondering if it was possible to get data for about
>>3200 companies*. I tried running the SML Lite version of Yahoo Get Quotes, and I got to about 200 companies before all the info just "whited out".
>>
>>
>>These are the data fields that I need:
>>
>>
>>-- Minority Interest (most recent quarter balance sheet)
>>-- 20-day Moving Average (I think I'll have to calculate this my self using Yahoo's historical price data)
>>-- 200-day Moving Average (This is on the Yahoo Key Statistics page)
>>
>
>
>
> - 2b.
-
Re: Get data for over 3000 companies?
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Sat Oct 8, 2011 10:14 am (PDT)
There's an item in the "Links" area of the group that documents the
specifics. However, this function invocation:
=smfGetCSVFile("http://finviz. com/export. ")ashx?v=151& c=1,65,52, 53,54
...would return something like:
Ticker Price 20-Day Simple Moving Average 50-Day Simple Moving Average 200-Day
Simple Moving Average A $31.37 -5.54% -9.39% -27.26% AA $9.71 -8.44%
-17.19% -36.02% AAC $9.72 -0.07% 0.13% 0.50% AACC $3.06 -16.05% -23.99%
-36.78%
On Sat, Oct 8, 2011 at 9:41 AM, Ron Spruell <hashky@yahoo.com > wrote:
>
> "You can get the 20-day and 200-day SMA from FinViz for all companies they
> cover in a single Internet access. "
>
> Do you have a sample of this some where on this website? I don't know how
> to do it.
>
>
Need to Reply?
Click one of the "Reply" links to respond to a specific message in the Daily Digest.
MARKETPLACE
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