Messages In This Digest (5 Messages)
- 1a.
- Re: Get data for over 3000 companies? From: Ron Spruell
- 1b.
- Re: Get data for over 3000 companies? From: Randy Harmelink
- 1c.
- Re: Get data for over 3000 companies? From: Ron Spruell
- 1d.
- Re: Get data for over 3000 companies? From: Randy Harmelink
- 2a.
- Re: Controlling updates From: Ron Spruell
Messages
- 1a.
-
Re: Get data for over 3000 companies?
Posted by: "Ron Spruell" hashky@yahoo.com hashky
Sun Oct 9, 2011 9:47 am (PDT)
Randy -
I got =smfGetCSVFile("http://finviz. com/export. ") to work.ashx?v=151& c=1,65,52, 53,54
I got =smfGetCSVFile("http://finviz. com/export. ") to work.ashx?v=151& t=MMM,NFLX& c=1,65,66, 67
But I could not get =smfGetCSVFile("http://finviz. com/export. "&sfJoin(A3:ashx?v=151& T= A9,",")&" &c="&sfJoin( B1:E1,"," )) to work.
I think the problems is in the sfJoin command. What is it? Is it part of smf_addin or Excel? What does the v=151 do? Or maybe, what is it?
I would like to pull in something like =smfGetCSVFile("http://finviz. com/export. ")ashx?v=151& t=MMM,NFLX& c=1,65,66, 67
where I can enter the Tickers in Column A.
I am running Windows XP Pro and Excel 2003.
Ron
>____________________ _________ ___
>From: Randy Harmelink <rharmelink@gmail.com >
>To: smf_addin@yahoogroups.com
>Sent: Saturday, October 8, 2011 12:14 PM
>Subject: Re: [smf_addin] Get data for over 3000 companies?
>
>
>
>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.
>>
>
>
>
> - 1b.
-
Re: Get data for over 3000 companies?
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Sun Oct 9, 2011 12:07 pm (PDT)
As the FinViz message indicated, sfJoin() is a personal UDF of mine. You
would need to add it either to the add-in or your workbook for it to be
available.
However, I did add an smfJoin() function that you could use if you are using
a beta version of the add-in, from 2011-02-16 or later. That's when I added
the new function to the add-in. It was such a useful function, I thought I
may as well add it to the add-in.
"v=151" is the output format that allows you to specify your own fields.
Other formats from FinViz have specific output fields. Go play with the
FinViz screener and you'll see the various output formats.
On Sun, Oct 9, 2011 at 9:47 AM, Ron Spruell <hashky@yahoo.com > wrote:
>
> I got =smfGetCSVFile("http://finviz. com/export. ")ashx?v=151& c=1,65,52, 53,54
> to work.
>
> I got =smfGetCSVFile("
> http://finviz.com/export. ") to work.ashx?v=151& t=MMM,NFLX& c=1,65,66, 67
>
> But I could not get =smfGetCSVFile("http://finviz. com/export. "&sfJoin(A3:ashx?v=151& T= A9,",")&" &c="&sfJoin( B1:E1,"," ))
> to work.
>
> I think the problems is in the sfJoin command. What is it? Is it part of
> smf_addin or Excel? What does the v=151 do? Or maybe, what is it?
>
> I would like to pull in something like =smfGetCSVFile("
> http://finviz.com/export. ")ashx?v=151& t=MMM,NFLX& c=1,65,66, 67
> where I can enter the Tickers in Column A.
>
> I am running Windows XP Pro and Excel 2003.
>
>
- 1c.
-
Re: Get data for over 3000 companies?
Posted by: "Ron Spruell" hashky@yahoo.com hashky
Sun Oct 9, 2011 2:10 pm (PDT)
O.K. I didn't read that far.
I got
=smfGetCSVFile("http://finviz. com/export. "&smfJoin(A3:ashx?v=111& T= A50,",")& "&c="&smfJoin( B1:E1,"," )) to work.
It will only pull in 48 Tickers and limited fields, e.g.,
No., Ticker, Company, Sector, Industry, Country, Market Cap
The error I get is #VALUE
When I export the screen, I get 961 Tickers.
This will only pull in 38 Tickers.
=smfGetCSVFile("http://finviz. com/export. "&smfJoin(B8:ashx?v=151& T= B46,",")& "&c="&smfJoin( H6:N6,"," ))
Again the error I get is #VALUE
I can vary the fields by entries in H6:N6 and possibly more.
What am I doing wrong?
I would like to pull in 451 specific Tickers and fields such as
Yesterday,s Closing Price
Current Price
Percent change from 52-week high
52-week high
52-week low
Growth Rate
sector
industry
average volume
Ron
>____________________ _________ ___
>From: Randy Harmelink <rharmelink@gmail.com >
>To: smf_addin@yahoogroups.com
>Sent: Sunday, October 9, 2011 2:07 PM
>Subject: Re: [smf_addin] Get data for over 3000 companies?
>
>
>
>As the FinViz message indicated, sfJoin() is a personal UDF of mine. You would need to add it either to the add-in or your workbook for it to be available.
>
>However, I did add an smfJoin() function that you could use if you are using a beta version of the add-in, from 2011-02-16 or later. That's when I added the new function to the add-in. It was such a useful function, I thought I may as well add it to the add-in.
>
>"v=151" is the output format that allows you to specify your own fields. Other formats from FinViz have specific output fields. Go play with the FinViz screener and you'll see the various output formats.
>
>
>On Sun, Oct 9, 2011 at 9:47 AM, Ron Spruell <hashky@yahoo.com > wrote:
>
>
>>
>>I got =smfGetCSVFile("http://finviz. com/export. ") to work.ashx?v=151& c=1,65,52, 53,54
>>
>>
>>I got =smfGetCSVFile("http://finviz. com/export. ") to work.ashx?v=151& t=MMM,NFLX& c=1,65,66, 67
>>
>>
>>But I could not get =smfGetCSVFile("http://finviz. com/export. "&sfJoin(A3:ashx?v=151& T= A9,",")&" &c="&sfJoin( B1:E1,"," )) to work.
>>
>>
>>I think the problems is in the sfJoin command. What is it? Is it part of smf_addin or Excel? What does the v=151 do? Or maybe, what is it?
>>
>>
>>I would like to pull in something like =smfGetCSVFile("http://finviz. com/export. ")ashx?v=151& t=MMM,NFLX& c=1,65,66, 67
>>where I can enter the Tickers in Column A.
>>
>>
>>I am running Windows XP Pro and Excel 2003.
>>
>
>
>
> - 1d.
-
Re: Get data for over 3000 companies?
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Sun Oct 9, 2011 2:55 pm (PDT)
You're only going to get 48 tickers or 38 tickers if that's all the tickers
you ask for (i.e. A3:A50 and B8:B46). You're only going to get limited
fields if that's all you ask for (i.e. B1:E1 is four fields, H6:N6 is seven
fields).
And "v=111" isn't going to allow you to specify which fields to grab. That's
one of those "specific output format" options I mentioned.
- "v=111" is the "Overview" output
- "v=121" is the "Valuation" output
- "v=131" is the "Ownership" output
- "v=141" is the "Performance" output
- "v=151" is the "Custom" output -- this is the only one where you can
specify your own fields
- "v=161" is the "Financial" output
- "v=171" is the "Technical" output
I don't know what you mean by #VALUE! errors if you have it working? Can
you attach or upload a copy of the workbook? I copied and pasted both of
your formulas below and I get the output results I would expect to see.
On Sun, Oct 9, 2011 at 2:09 PM, Ron Spruell <hashky@yahoo.com > wrote:
>
> O.K. I didn't read that far.
>
> I got
> =smfGetCSVFile("http://finviz. com/export. "&smfJoin(A3:ashx?v=111& T= A50,",")& "&c="&smfJoin( B1:E1,"," ))
> to work.
>
> It will only pull in 48 Tickers and limited fields, e.g.,
> No., Ticker, Company, Sector, Industry, Country, Market Cap
> The error I get is #VALUE
>
> When I export the screen, I get 961 Tickers.
>
> This will only pull in 38 Tickers.
> =smfGetCSVFile("http://finviz. com/export. ashx?v=151& T=
> "&smfJoin(B8:B46,",")& "&c="&smfJoin( H6:N6,"," ))
> Again the error I get is #VALUE
>
> I can vary the fields by entries in H6:N6 and possibly more.
>
> What am I doing wrong?
>
> I would like to pull in 451 specific Tickers and fields such as
> Yesterday,s Closing Price
> Current Price
> Percent change from 52-week high
> 52-week high
> 52-week low
> Growth Rate
> sector
> industry
> average volume
>
>
- 2a.
-
Re: Controlling updates
Posted by: "Ron Spruell" hashky@yahoo.com hashky
Sun Oct 9, 2011 10:19 am (PDT)
Thanks Randy -
So many tips and tricks, so little time.
I actually use the NOW() function because I want the last time I ran an update. I think I can do as you suggest and speed up the spreadsheet. I will give it a try.
Ron
>____________________ _________ ___
>From: Randy Harmelink <rharmelink@gmail.com >
>To: smf_addin@yahoogroups.com
>Sent: Saturday, October 8, 2011 11:20 AM
>Subject: Re: [smf_addin] Controlling updates
>
>
>
>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(RCHGetYahooQuote s("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(), =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?
>>
>
>
>
>
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