Senin, 05 Desember 2011

[smf_addin] Digest Number 2036[2 Attachments]

Messages In This Digest (9 Messages)

Messages

1a.

600 stock data (quarterly earnings and dates) from Zacks Media site

Posted by: "V.Shankar" tarakayan@yahoo.com   tarakayan

Sun Dec 4, 2011 3:31 am (PST)



I am using Zacks site for this data by downloading into excel file, but need to update this often. How do I link this Zack site table to excel file? I tried web query, but didn't work.

I have been using MSN money central for stock quotes quite a while. This works great. Hope I can do the same with Zack site.

thanks,
Shankar

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (8)
1b.

Re: 600 stock data (quarterly earnings and dates) from Zacks Media s

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Sun Dec 4, 2011 4:39 am (PST)



Not sure exactly what you're asking...

After saving a screen at Zacks, you can grab the data with something like
(substitute your screen number for "xxxxxx"), as long as you have created
the proper security cookie in EXCEL Web Query or IE:

=RCHGetHTMLTable("http://screener.zacks.com/screens/xxxxxx/run
",">Company",-1,"",1)

Company Name Ticker Next EPS Report Date AAC ACOUST TECH AACAY N/A ARCTIC
CAT INC ACAT 20120126 ADV AMER CASH AEA 20120215 AETNA INC-NEW AET
20120210
...or try FinViz (see "Links" area of group for details):

=smfGetCSVFile("http://finviz.com/export.ashx?v=151&c=1,68")

Ticker Earnings Date A 11/15/2011 4:30:00 PM AA 1/9/2012 4:30:00 PM
AACC 11/1/2011
4:00:00 PM AADR AAME 11/11/2011 AAN 10/24/2011 4:30:00 PM
On Sun, Dec 4, 2011 at 4:26 AM, V.Shankar <tarakayan@yahoo.com> wrote:

> I am using Zacks site for this data by downloading into excel file, but
> need to update this often. How do I link this Zack site table to excel
> file? I tried web query, but didn't work.
>
> I have been using MSN money central for stock quotes quite a while. This
> works great. Hope I can do the same with Zack site.
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (8)
1c.

Re: 600 stock data (quarterly earnings and dates) from Zacks Media s

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Sun Dec 4, 2011 4:59 am (PST)



Correction -- the previous Zack's formula I gave just gets the first page
of results from the screener. This exports the results of a screen number
entered in cell B2:

=smfGetCSVFile("http://screener.zacks.com/export?dummy=
"&$B$2&RCHGetTableCell("http://screener.zacks.com/screens/
"&$B$2&"/run",3,">Company",,,,1))

On Sun, Dec 4, 2011 at 5:39 AM, Randy Harmelink <rharmelink@gmail.com>wrote:

> Not sure exactly what you're asking...
>
> After saving a screen at Zacks, you can grab the data with something like
> (substitute your screen number for "xxxxxx"), as long as you have created
> the proper security cookie in EXCEL Web Query or IE:
>
> =RCHGetHTMLTable("http://screener.zacks.com/screens/xxxxxx/run
> ",">Company",-1,"",1)
>
> Company Name Ticker Next EPS Report Date AAC ACOUST TECH AACAY N/A ARCTIC
> CAT INC ACAT 20120126 ADV AMER CASH AEA 20120215 AETNA INC-NEW AET
> 20120210
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (8)
1d.

Re: 600 stock data (quarterly earnings and dates) from Zacks Media s

Posted by: "V. Shankar" tarakayan@yahoo.com   tarakayan

Sun Dec 4, 2011 8:02 am (PST)

[Attachment(s) from V. Shankar included below]

Thanks Randy for your message.  I thought I reply with an example so that my question is clear now.

1. I download an excel file from Zack from the site http://www.zacks.com/mediaroom/estimates/estimates_reports.php?valtype=SP500

2. I have attached the zack excel file along with my analysis tab to pull out information in the format I want viz.:  

--quarter ending dates for consecutive years, eps, next report date....etc for 3 years.

--when you type a ticker symbol in "A11", you will see eps reports in a format that is easier to read

Zack's excel file has 5598 line items. Just to reduce the size of the file (for this attachment), I am showing only around 100 ticker symbols.

The issue here is:  I need to import/download Zack's file often so that the info is up to date  (Zack's file in their website updates every 10 minutes)

My question is: I would like the first tab "zack" to update automatically without the need to download and overwrite my old file manually.  Every time I download, I need to maintain the format, naming the range etc. 

thanks,
Shankar

PS: I use excel web query for downloading stock info that gives daily price movements, volume, 52w h&l, pe, shares outstanding etc.  I tried the same for Zack, but not able to download the data...

>________________________________
> From: Randy Harmelink <rharmelink@gmail.com>
>To: smf_addin@yahoogroups.com
>Sent: Sunday, December 4, 2011 6:29 PM
>Subject: Re: [smf_addin] 600 stock data (quarterly earnings and dates) from Zacks Media site
>
>
> 
>Correction -- the previous Zack's formula I gave just gets the first page of results from the screener. This exports the results of a screen number entered in cell B2:
>
>=smfGetCSVFile("http://screener.zacks.com/export?dummy="&$B$2&RCHGetTableCell("http://screener.zacks.com/screens/"&$B$2&"/run",3,">Company",,,,1))
>
>
>On Sun, Dec 4, 2011 at 5:39 AM, Randy Harmelink <rharmelink@gmail.com> wrote:
>
>Not sure exactly what you're asking...
>>
>>After saving a screen at Zacks, you can grab the data with something like (substitute your screen number for "xxxxxx"), as long as you have created the proper security cookie in EXCEL Web Query or IE:
>>
>>=RCHGetHTMLTable("http://screener.zacks.com/screens/xxxxxx/run",">Company",-1,"",1)
>>
>>
>>Company Name
>>Ticker
>>Next EPS Report Date
>>AAC ACOUST TECH AACAY N/A
>>ARCTIC CAT INC ACAT 20120126
>>ADV AMER CASH AEA 20120215
>>AETNA INC-NEW AET 20120210
>>
>
>
>
>

Attachment(s) from V. Shankar

1 of 1 File(s)

TOP_600sample.xls
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (8)
1e.

Re: 600 stock data (quarterly earnings and dates) from Zacks Media s

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Sun Dec 4, 2011 8:56 am (PST)



Are you saying that they are updating that XLS file every 10 minutes, but
not the data pages on their web site?

I ask, because the add-in could easily retrieve all of the data items on
your "analysis" sheet from this web page:

http://www.zacks.com/research/report.php?type=estimates&t=AEGN

And, BTW, your "analysis" sheet is wrong -- it shows $0.44 for Mar-11 and
$0.08 for Dec-10. However, the web page above shows them reversed, as $0.08
for Mar-11 and $0.44 for Dec-10.

I wrote the add-in functions because of frustrations I had with web
queries, and I wanted a more exact way to extract data. However, I don't
see a way for the add-in functions to access the XLS file -- which is why
I'm asking if the web page above wouldn't substitute. It seems a shame to
download such a large file when you are just extracting data from it one
ticker at a time.

In any case, if you want the file downloaded and formatted regularly, why
not just write some VBA to do it and automate the process?

On Sun, Dec 4, 2011 at 9:02 AM, V. Shankar <tarakayan@yahoo.com> wrote:

>
> Thanks Randy for your message. I thought I reply with an example so that
> my question is clear now.
>
> 1. I download an excel file from Zack from the site
> http://www.zacks.com/mediaroom/estimates/estimates_reports.php?valtype=SP500
>
> 2. I have attached the zack excel file along with my analysis tab to pull
> out information in the format I want viz.:
>
> --quarter ending dates for consecutive years, eps, next report date....etc
> for 3 years.
>
> --when you type a ticker symbol in "A11", you will see eps reports in a
> format that is easier to read
>
> Zack's excel file has 5598 line items. Just to reduce the size of the file
> (for this attachment), I am showing only around 100 ticker symbols.
>
> The issue here is: I need to import/download Zack's file often so that
> the info is up to date (Zack's file in their website updates every 10
> minutes)
>
> My question is: I would like the first tab "zack" to update automatically
> without the need to download and overwrite my old file manually. Every
> time I download, I need to maintain the format, naming the range etc.
>
> thanks,
> Shankar
>
> PS: I use excel web query for downloading stock info that gives daily
> price movements, volume, 52w h&l, pe, shares outstanding etc. I tried the
> same for Zack, but not able to download the data...
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (8)
1f.

Re: 600 stock data (quarterly earnings and dates) from Zacks Media s

Posted by: "Mike McQuaid" mikemcq802@yahoo.com   mikemcq802

Sun Dec 4, 2011 9:15 am (PST)



Why can't you just download the Zacks spreadsheet into its own workbook.

Then, put your analysis in a separate workbook and just link to the Zacks
downloaded one?

You could then download Zacks whenever you want and you'd always have that
data available to your Analysis workbook

Example from Analysis workbook:

='c:\data\[zacksdata.xls]Tab1'!B5

_____

From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf
Of Randy Harmelink
Sent: Sunday, December 04, 2011 11:56 AM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] 600 stock data (quarterly earnings and dates) from
Zacks Media site

Are you saying that they are updating that XLS file every 10 minutes, but
not the data pages on their web site?

I ask, because the add-in could easily retrieve all of the data items on
your "analysis" sheet from this web page:

http://www.zacks.com/research/report.php?type=estimates
<http://www.zacks.com/research/report.php?type=estimates&t=AEGN> &t=AEGN

And, BTW, your "analysis" sheet is wrong -- it shows $0.44 for Mar-11 and
$0.08 for Dec-10. However, the web page above shows them reversed, as $0.08
for Mar-11 and $0.44 for Dec-10.

I wrote the add-in functions because of frustrations I had with web queries,
and I wanted a more exact way to extract data. However, I don't see a way
for the add-in functions to access the XLS file -- which is why I'm asking
if the web page above wouldn't substitute. It seems a shame to download such
a large file when you are just extracting data from it one ticker at a time.

In any case, if you want the file downloaded and formatted regularly, why
not just write some VBA to do it and automate the process?

On Sun, Dec 4, 2011 at 9:02 AM, V. Shankar <tarakayan@yahoo.com> wrote:

Thanks Randy for your message. I thought I reply with an example so that my
question is clear now.

1. I download an excel file from Zack from the site
http://www.zacks.com/mediaroom/estimates/estimates_reports.php?valtype=SP500

2. I have attached the zack excel file along with my analysis tab to pull
out information in the format I want viz.:

--quarter ending dates for consecutive years, eps, next report date....etc
for 3 years.

--when you type a ticker symbol in "A11", you will see eps reports in a
format that is easier to read

Zack's excel file has 5598 line items. Just to reduce the size of the file
(for this attachment), I am showing only around 100 ticker symbols.

The issue here is: I need to import/download Zack's file often so that the
info is up to date (Zack's file in their website updates every 10 minutes)

My question is: I would like the first tab "zack" to update automatically
without the need to download and overwrite my old file manually. Every time
I download, I need to maintain the format, naming the range etc.

thanks,
Shankar

PS: I use excel web query for downloading stock info that gives daily price
movements, volume, 52w h&l, pe, shares outstanding etc. I tried the same
for Zack, but not able to download the data...

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (8)
1g.

Re: 600 stock data (quarterly earnings and dates) from Zacks Media s

Posted by: "V. Shankar" tarakayan@yahoo.com   tarakayan

Sun Dec 4, 2011 6:16 pm (PST)

[Attachment(s) from V. Shankar included below]

Thanks Mike for your note.  This is exactly what I do.  I keep Zacks file separately and link it to my other "shopping list.xlm"  file for analysis.....see my file attached.

As I am used to having msn stock quotes getting updated automatically, I was thinking of Zacks file doing the same.

>________________________________
> From: Mike McQuaid <mikemcq802@yahoo.com>
>To: smf_addin@yahoogroups.com
>Sent: Sunday, December 4, 2011 10:45 PM
>Subject: RE: [smf_addin] 600 stock data (quarterly earnings and dates) from Zacks Media site
>
>
> 
>Why can't you just download the Zacks spreadsheet into its own
workbook.
> 
>Then, put your analysis in a separate workbook and just link
to the Zacks downloaded one?
> 
>You could then download Zacks whenever you want and you'd
always have that data available to your Analysis workbook
> 
>Example from Analysis workbook:
> 
>='c:\data\[zacksdata.xls]Tab1'!B5
>
>
>________________________________
> From: smf_addin@yahoogroups.com
[mailto:smf_addin@yahoogroups.com] On Behalf Of Randy
Harmelink
>Sent: Sunday, December 04, 2011 11:56 AM
>To: smf_addin@yahoogroups.com
>Subject: Re: [smf_addin] 600 stock data
(quarterly earnings and dates) from Zacks Media site
>
> 
>Are you saying that they are updating that XLS file every 10 minutes, but not
the data pages on their web site?
>
>I ask, because the add-in could easily
retrieve all of the data items on your "analysis" sheet from this web
page:
>
>http://www.zacks.com/research/report.php?type=estimates&t=AEGN
>
>And,
BTW, your "analysis" sheet is wrong -- it shows $0.44 for Mar-11 and $0.08 for
Dec-10. However, the web page above shows them reversed, as $0.08 for Mar-11 and
$0.44 for Dec-10.
>
>I wrote the add-in functions because of frustrations I
had with web queries, and I wanted a more exact way to extract data. However, I
don't see a way for the add-in functions to access the XLS file -- which is why
I'm asking if the web page above wouldn't substitute. It seems a shame to
download such a large file when you are just extracting data from it one ticker
at a time.
>
>In any case, if you want the file downloaded and formatted
regularly, why not just write some VBA to do it and automate the process?
>
>
>On Sun, Dec 4, 2011 at 9:02 AM, V. Shankar <tarakayan@yahoo.com> wrote:
>
>
>>
>>Thanks Randy for your message.  I thought I reply with an example so that my question is clear now.
>>
>>
>>1. I download an excel file from Zack from the site http://www.zacks.com/mediaroom/estimates/estimates_reports.php?valtype=SP500
>>
>>
>>2. I have attached the zack excel file along with my analysis tab to pull out information in the format I want viz.:  
>>
>>
>>--quarter ending dates for consecutive years, eps, next report date....etc for 3 years.
>>
>>
>>--when you type a ticker symbol in "A11", you will see eps reports in a format that is easier to read
>>
>>
>>Zack's excel file has 5598 line items. Just to reduce the size of the file (for this attachment), I am showing only around 100 ticker symbols.
>>
>>
>>The issue here is:  I need to import/download Zack's file often so that the info is up to date  (Zack's file in their website updates every 10 minutes)
>>
>>
>>My question is: I would like the first tab "zack" to update automatically without the need to download and overwrite my old file manually.  Every time I download, I need to maintain the format, naming the range etc. 
>>
>>
>>thanks,
>>Shankar
>>
>>
>>PS: I use excel web query for downloading stock info that gives daily price movements, volume, 52w h&l, pe, shares outstanding etc.  I tried the same for Zack, but not able to download the data...
>>
>
>
>
>

Attachment(s) from V. Shankar

1 of 1 File(s)

ShoppingListsample.xlsm
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (8)
1h.

Re: 600 stock data (quarterly earnings and dates) from Zacks Media s

Posted by: "V. Shankar" tarakayan@yahoo.com   tarakayan

Sun Dec 4, 2011 6:48 pm (PST)



I would think that both should get updated the same time because the XLS file must be giving the same info in a table form--the figures can't different.  Zack confirmed to me that "earnings tables in our media room on Zacks.com update every 15 minutes during the business day. Regarding the 3rd column on the table titled Most Recent Qtr. End Date...."

As Mike mentioned in his reply, I do maintain a separate file where I do research on 100 or more ticker symbols (or to just look for data, one ticker at a time, whenever a symbol is discussed in WSJ or Barrons or IBD or on CNBC etc).  Unfortunately, a large data file is needed for this purpose without having to go to a website.  I don't see a problem with this.

Currently, the standalone Zacks file works well for me, except I need to update periodically.  I will write a VBA to format it the way I want.  I am more used to seeing Stock Quotes provided by MSN Money (in an excel file) with over 200 ticker symbols getting updated instantly during market hours, 20 mins delay of course.

I will try your add-in functions and see how they work.  

BTW, your observation on eps is correct, they got reversed because in the sample file that I sent you, I must have dragged the cell formula, instead of using the right columns. They work quite well in my other links.

>________________________________
> From: Randy Harmelink <rharmelink@gmail.com>
>To: smf_addin@yahoogroups.com
>Sent: Sunday, December 4, 2011 10:26 PM
>Subject: Re: [smf_addin] 600 stock data (quarterly earnings and dates) from Zacks Media site
>
>
> 
>Are you saying that they are updating that XLS file every 10 minutes, but not the data pages on their web site?
>
>I ask, because the add-in could easily retrieve all of the data items on your "analysis" sheet from this web page:
>
>http://www.zacks.com/research/report.php?type=estimates&t=AEGN
>
>And, BTW, your "analysis" sheet is wrong -- it shows $0.44 for Mar-11 and $0.08 for Dec-10. However, the web page above shows them reversed, as $0.08 for Mar-11 and $0.44 for Dec-10.
>
>I wrote the add-in functions because of frustrations I had with web queries, and I wanted a more exact way to extract data. However, I don't see a way for the add-in functions to access the XLS file -- which is why I'm asking if the web page above wouldn't substitute. It seems a shame to download such a large file when you are just extracting data from it one ticker at a time.
>
>In any case, if you want the file downloaded and formatted regularly, why not just write some VBA to do it and automate the process?
>
>
>On Sun, Dec 4, 2011 at 9:02 AM, V. Shankar <tarakayan@yahoo.com> wrote:
>
>
>>
>>Thanks Randy for your message.  I thought I reply with an example so that my question is clear now.
>>
>>
>>1. I download an excel file from Zack from the site http://www.zacks.com/mediaroom/estimates/estimates_reports.php?valtype=SP500
>>
>>
>>2. I have attached the zack excel file along with my analysis tab to pull out information in the format I want viz.:  
>>
>>
>>--quarter ending dates for consecutive years, eps, next report date....etc for 3 years.
>>
>>
>>--when you type a ticker symbol in "A11", you will see eps reports in a format that is easier to read
>>
>>
>>Zack's excel file has 5598 line items. Just to reduce the size of the file (for this attachment), I am showing only around 100 ticker symbols.
>>
>>
>>The issue here is:  I need to import/download Zack's file often so that the info is up to date  (Zack's file in their website updates every 10 minutes)
>>
>>
>>My question is: I would like the first tab "zack" to update automatically without the need to download and overwrite my old file manually.  Every time I download, I need to maintain the format, naming the range etc. 
>>
>>
>>thanks,
>>Shankar
>>
>>
>>PS: I use excel web query for downloading stock info that gives daily price movements, volume, 52w h&l, pe, shares outstanding etc.  I tried the same for Zack, but not able to download the data...
>>
>
>
>
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (8)
2a.

Re: Distribute Spreadsheet with SMF Add-In

Posted by: "dguillett1" dguillett1@gmail.com   donaldb36

Sun Dec 4, 2011 5:22 am (PST)



Randy, Of course you are correct. I was thinking without selection since I usually hate to select. I'm getting old.....
.
Don Guillett
SalesAid Software
dguillett1@gmail.com

From: Randy Harmelink
Sent: Saturday, December 03, 2011 6:17 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Re: Distribute Spreadsheet with SMF Add-In

I just have it run for a single worksheet. Primarily because that's pretty much all I ever do.

It's easy to change though -- instead of:

ActiveSheet.Cells.Replace ...

Just do something like:

ActiveWorkbook.Sheets.Select
Selection.Cells.Replace ...

The first statement just selects all sheets, and does the replacement on that selection instead of just the active sheet...

On Sat, Dec 3, 2011 at 4:41 PM, bryankam10 <bryan.a.kam@gmail.com> wrote:

I was wondering, the macro you have written in the below post to replace the location add-in, does that work only in a single worksheet, or the whole workbook? I've written a similar macro to run when a workbook automatically opens, but for some reason when the macro code executes, it only does find and replace for the one worksheet and not all the worksheets in the workbook. Please let me know if you've encountered this problem, and if you've managed to find a way to fix it.

Thanks,
Bryan

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> Unfortunately, this is a common situation when workbooks are shared. It
> occurs because when you save your EXCEL file,MicroSoft decided to save the
> hard-coded location of the add-in in the cell formula, so that EXCEL knows
> the function comes from an external source. Then, when the file is opened,
> EXCEL looks for that external source. If it finds it, it resolves the link
> and removes the hard-coded location -- all is well. However, if it doesn't
> find the external source, EXCEL marks it as an unresolved link, which makes
> the cell non-functional until the link error is resolved.
>
> As you note, a manual change, cell by cell, does fix the problem. If you
> check EXCEL's menus, you find a way to > Edit > Links, which is another way
> to solve the issue for an entire workbook. However, I've found it work
> differently on different versions of EXCEL and was not a reliable way to fix
> link errors.
>
> The quickest and most reliable way I've found to fix the issue is to do a
> "Find and Replace All" (keyboard shortcut Ctrl+H), replacing all hard-coded
> locations:
>
> 'C:\User Name\Application
> Data\Microsoft\AddIns\RCH_Stock_Market_Functions.xla'!
>
> ...with nothing. Once that is done and the file is saved, that person's
> hard-coded add-in location will be saved with the formulas, so that the next
> time they open the file on their computer, the links will be resolved
> automatically.
>
> It's one reason I tried to go with a standard folder for the location of the
> add-in in the installation instructions. If everyone used the same location,
> this would never be an issue.
>
> Unfortunately, even I had to violate that, because Vista wouldn't let me
> update the add-in's XLA file while it was located in a subdirectory of the
> "Program Files" folder. The "Find and Replace" process could be automated --
> I have a macro I manually run to do it as needed when I open files that used
> to use my old location:
>
> Public Sub smfFixLinks()
> ActiveSheet.Cells.Replace _
> What:="'C:\Program Files\SMF
> Add-In\RCH_Stock_Market_Functions.xla'!", _
> Replacement:="", _
> LookAt:=xlPart, _
> SearchOrder:=xlByRows, _
> MatchCase:=False, _
> SearchFormat:=False, _

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (15)
Recent Activity
Visit Your Group
Yahoo! Finance

It's Now Personal

Guides, news,

advice & more.

Ads on Yahoo!

Learn more now.

Reach customers

searching for you.

Yahoo! News

Get it all here

Breaking news to

entertainment news

Need to Reply?

Click one of the "Reply" links to respond to a specific message in the Daily Digest.

Create New Topic | Visit Your Group on the Web
MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.

Diposting oleh EXCEL FEED LEARNING di 02.36

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)