Messages In This Digest (6 Messages)
- 1a.
- Re: Get data for over 3000 companies? From: Ron Spruell
- 1b.
- Re: Get data for over 3000 companies? [2 Attachments] From: Randy Harmelink
- 1c.
- Re: Get data for over 3000 companies? [2 Attachments] From: Randy Harmelink
- 2.
- Invitation to connect on LinkedIn From: Gennady Kupershteyn
- 3a.
- How to create add-in From: Cornelius
- 3b.
- Re: How to create add-in From: Randy Harmelink
Messages
- 1a.
-
Re: Get data for over 3000 companies?
Posted by: "Ron Spruell" hashky@yahoo.com hashky
Mon Oct 10, 2011 5:05 am (PDT)
[Attachment(s) from Ron Spruell included below]
Randy -
Here is the spreadsheet with the #VALUE! output. FinViz-temp1-value.xls The array is in cells H7:Q457. The equation is
=smfGetCSVFile("http://finviz. com/export. "&smfJoin(B8:ashx?v=151& T= B52,",")& "&c="&smfJoin( H6:Q6,"," ))
I can make the #VALUE! go away by deleting the value in Cell Q6. And I can make it return by changing the B8:B52 to B8:B53.
I have also attached Quality Stocks-01.01.xls. This is a busy spreadsheet that takes about 10 minutes to calculate (SMFForceRecalculat ion). I calculate it once per day. After that, at the Main Tab, I just calculate the Main sheet (Shift-F9) which has a minimum of Internet calls. It only take a few seconds to update.
I would appreciate your comments on it if you have time.
I would really like to get FinViz working because with at of the uncertainty surrounding Yahoo, I am worried that this source may go away.
Ron
>____________________ _________ ___
>From: Randy Harmelink <rharmelink@gmail.com >
>To: smf_addin@yahoogroups.com
>Sent: Sunday, October 9, 2011 4:55 PM
>Subject: Re: [smf_addin] Get data for over 3000 companies?
>
>
>
>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" outputI 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. "&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
>>
>
>
>
>Attachment(s) from Ron Spruell
2 of 2 File(s)
- 1b.
-
Re: Get data for over 3000 companies? [2 Attachments]
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Mon Oct 10, 2011 9:46 am (PDT)
I don't see any #VALUE! output on this end. I changed B8:B52 to B8:B457 and
still no #VALUE! output.
However, I do have two items to take note of:
- FinViz output is returned sorted by ticker symbol, so having things
side by side like that probably isn't giving you the results you want.
- I'm only seeing 444 stocks returned instead of 450. I suspect ticker
symbols that aren't recognized/used by FinViz.
I think what you'll need to do is put the FinViz output in a separate
worksheet and then use VLOOKUP() functions to place it into your data
analysis grid.
On Mon, Oct 10, 2011 at 5:05 AM, Ron Spruell <hashky@yahoo.com > wrote:
>
> Here is the spreadsheet with the #VALUE! output. FinViz-temp1-value.xls
> The array is in cells H7:Q457. The equation is
> =smfGetCSVFile("http://finviz. com/export. ashx?v=151& T=
> "&smfJoin(B8:B52,",")& "&c="&smfJoin( H6:Q6,"," ))
>
> I can make the #VALUE! go away by deleting the value in Cell Q6. And I can
> make it return by changing the B8:B52 to B8:B53.
>
- 1c.
-
Re: Get data for over 3000 companies? [2 Attachments]
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Mon Oct 10, 2011 9:59 am (PDT)
The 10 minutes of recalculation is because cell E5 in worksheet Web_Data
refers to cell F2 of worksheet Main, which contains the NOW() function. That
means EVERY time you change something in the workbook, the value of NOW()
changes, triggering a recalculation of all of those smfPricesByDates()
functions that use it as one of the dates being retrieved.
If you always plan to get the current closing price, I would drop that date
from the smfPricesByDates() function and replace it with an array-entered
RCHGetYahooQuotes() and just get the current close from it. You're already
using that function for other data items, so it wouldn't really change it
much to add one additional data element.
Also, all of your smfPricesBetween() use the TODAY() function, which also
makes them volatile and recalculate with every change.
On Mon, Oct 10, 2011 at 5:05 AM, Ron Spruell <hashky@yahoo.com > wrote:
>
> I have also attached Quality Stocks-01.01.xls. This is a busy spreadsheet
> that takes about 10 minutes to calculate (SMFForceRecalculation). I
> calculate it once per day. After that, at the Main Tab, I just calculate
> the Main sheet (Shift-F9) which has a minimum of Internet calls. It only
> take a few seconds to update.
>
>
- 2.
-
Invitation to connect on LinkedIn
Posted by: "Gennady Kupershteyn" gennady17@yahoo.com gennady17
Mon Oct 10, 2011 10:30 am (PDT)
I'd like to add you to my professional network on LinkedIn.
- Gennady
Gennady Kupershteyn
Chief Market Strategist/Managing Partner at Capitalist Bull, LP
Greater New York City Area
Confirm that you know Gennady Kupershteyn:
https://www.linkedin. com/e/-tv9qrq- gtlqm35y- 4n/isd/451257652 9/JkLDFEUh/ ?hs=false& tok=39-7d32O6Wg4 Y1
--
You are receiving Invitation to Connect emails. Click to unsubscribe:
http://www.linkedin.com/e/-tv9qrq- gtlqm35y- 4n/qCcx50e1YkhpV bt_4Vjs2rl0SquVe Hm_8cBYICB/ goo/smf_addin% 40yahoogroups% 2Ecom/20061/ I1563206962_ 1/?hs=false& tok=3NFbJ_ e1CWg4Y1
(c) 2011 LinkedIn Corporation. 2029 Stierlin Ct, Mountain View, CA 94043, USA.
- 3a.
-
How to create add-in
Posted by: "Cornelius" mcpeak241@gmail.com mcpeak241
Mon Oct 10, 2011 1:26 pm (PDT)
Hi Randy,
Do you have any advice as to books, courses or information on how to create an excel macro similar to yours in which you can pull specific website? I have been using your add-in for sometime now and would like to learn how to create something similar.
- 3b.
-
Re: How to create add-in
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Mon Oct 10, 2011 1:38 pm (PDT)
I learned most of my process between trial and error and Google searches.
There are a lot of examples out there.
The "Overview" document in the files area has some links to general
information about add-ins.
On Mon, Oct 10, 2011 at 1:25 PM, Cornelius <mcpeak241@gmail.com > wrote:
>
> Do you have any advice as to books, courses or information on how to create
> an excel macro similar to yours in which you can pull specific website? I
> have been using your add-in for sometime now and would like to learn how to
> create something similar.
>
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