Earnings.xls automatically downloads and fills in the expected earnings dates from zacks and yahoo.
1. Download the file earnings.xls (last updated 4/13/2013 * updated code so that dates from Yahoo work once again.)
Initial Setup: (steps 2-3 only have to be done once)
2. Press alt + F11
3. From the Microsoft Visual Basic Menu, Select TOOLS, select REFERENCES, then find “Microsoft Internet Controls” and place a checkmark next to it. Click OK and Exit everything.
If Microsoft Internet Controls is not listed, click the browse button and browse to system32shdocvw.dll. Then it will appear in the list.
4. Import, paste or type symbols into column A
5. Press the “Get Earnings” button.
Earnings dates will fill in.
If earnings.xls does not pull in the dates:
Earnings.xls opens 3 2 websites (in hidden instances of Internet explorer) and parses for the earnings dates. It waits until the page is done loading before moving forward. Firewalls, adblockers, modified host files etc can prevent a website from completely loading. Here is how to fix this problem.
Open each of the following links in Internet Explorer (It has to be IE)
At the bottom left of IE you must get the message “Done” (not 1 item remaining etc).
http://www.zacks.com/research/report.php?type=estimates&t=MSFT
http://biz.yahoo.com/research/earncal/m/msft.html
http://clearstation.etrade.com/cgi-bin/details?Symbol=msft **
Nice work!
I am so grateful for your wonderful earnings downloader. For ages I been searching high and low for some way get earnings dates for my watchlists without having to manually enter each one. Not only did I search all over the net but I even have accounts with Thinkorswim, Investools, and Worden TC Telechart and none of them allowed for this. I had actually come across your site months ago but kept getting runtime error #9 when I tried the download so I gave up. Today however, it worked but I'm not sure why. Now though I get a runtime error #5 but it works just great despite that. A million thanks.
Gillian.
Gillian,
Thanks for informing me of this issue. I have updated earnings.xls so that the error no longer occurs. The issue is that currently earnings data is missing from most all stocks on the clearstation website which is where the dates in column two are from. This appears to be a temporary problem but at least when there is no data the program will no longer error. Instead it will be skipped but earnings dates will still appear from yahoo/zacks.
Thank you so much for resolving the problem so quickly
At the risk of being redundant because I did send an email the gmail account but would you also be kind enough to tell me what file format to save as? When I try various things, I either can't reopen it or I get runtime error #9 out of range.
Save as "Excel 97-2003 workbook (*xls)". Also the file should not be renamed or it will error. (If you have Excel 2010 and that does not solve the issue you might also need to lower security settings File>Options>Trust Center>Trust Center Settings>Macro Settings> then Enable all macros and a check next to to "trust access to the vba project object model")
I have an error in the VBA here:
Do Until OIE2.ReadyState = READYSTATE_COMPLETE
my IE is in French.
How can I resolve the problem?
Stephane
Hi,
I have window 7 and Office 2007
I don't "Microsoft Internet Controls" or file name "shdocvw.dll"
it is not listed on Microsoft Visual Basic Menu, Select TOOLS, select REFERENCE..
Please advise...
Thanks
RajaJani
RajaJani,
Try the this location and file name ieframe.dll. The location and file name to can differ.
Is this still maintained? Until last week I got runtime error 9 but it would work if I opened the macro and just ran "XXXZacksDOTcom" .
If I run it now all dates are 3/14/2013. Zacks.com was under construction this week..
Marco, It's likely that the issue with zacks was temporary. I have tested and am able to get dates with no issues. Reporting the problem via comments is the best way to notify me of any issue and get an update. Thanks.
Neil, your screenshot also has all zack's returns defaulting to 100 days away? It appears there is somthing going on at Zack's http://finance.groups.yahoo.com/group/smf_addin/message/20475
Marco, Thanks again for pointing out the issue. Earnings.xls has been updated and once again works properly with zacks.
Thanks for the quick update. One more thing I'd like to point out is calculation of the best date. Would it not be a better option to disregard a negative value which likely refers to past earnings date?
AIG 11/1/2012 -35 2/21/2013 77 11/1/2012
When I bring up Yahoo and Zacks in internet explorer and then start the program, IE stops working. Is that a bug with one of the websites?
John, first test that you can open zacks in IE enter a symbol and have the page load with no errors. If that works there may have been a temporary issue with their site.
Thank you very much for an extremely helpful tool !!!
Wow! I too have been looking for some tool like this. Quite exceptional! You get a BIG "Atta Boy!" from me. May I suggest adding a sheet called "Dividend" that provides the latest dates for Ex-Dividend and Dividend for the same stock list. This would be really sweet!
How can I get this to work on a Mac with Excel?
Although I make effort for my files to work with excel 2003 and newer, any ability for my files to run on mac versions of office are dependent on compatibility between Mac/PC versions of Excel, as I do not own a mac and do not test on Mac versions.
Does this work strictly for NYSE stocks? I have tried with some Canadian stocks and it hasn't worked. Do you know if there is a way to make it accurately retreive them?
The data comes from yahoo so the symbol must be available from yahoo finance. For example symbol AAH needs to be entered as AAH.TO and for any symbol you need to enter it as it appears after the "=" in the URL like the one here> http://ca.finance.yahoo.com/q?s=AAH.TO
Hi Neil, just wanted to let you know that Zacks is not working, I get the 100 days pending message. I did some research and think that you need to use: "http://www.zacks.com/stock/quote/Ticker/detailed-estimates" instead of just "http://www.zacks.com/stock/quote/Ticker/".
I look forward to hearing back from you, and also to see your finalized spreadsheet for the naked options!!
I had a similar issue, all Yahoo values returned earnings dates 100 days away. In my case it seemed to be associated with updating from IE9 to IE10, restoring to the previous version of internet explorer resolved it.
Hi Neil - sorry to be a pest. Had used this download once before and LOVED it, but now the GET EARNINGS button in the same file does nothing. Tried to re-download the file, did AltF11 again in case something came undone, confirmed Microsoft Internet Controls was checked under References, but still nothing. Can you recommend any next steps? Loved your program and an anxiously looking forward to your release of your Selling Options program. You do GREAT work !!! Thanks for your help!
Thanks to those who have commented about the issues with earnings.xls. These problems are expected as the various sources change their format. In my test zacks was working correctly. Yahoo was not working but has now been fixed. Eric, the problem you describe of nothing happening most likely means that macros are disabled in your excel settings or the security settings are too high. (Also if you use Excel 2007 or newer, you might try saving the file to .xlsm filetype and then running.)
Hi Neil,
clicking on "Get Earnings" resulted in the Microsoft Visual Basic error message
"Run-time error '9':
Subscript out of range
No results displayed from
http://www.zacks.com/research/report.php?type=estimates&t=MSFT
with thanks, Andy
Please ignore my earlier post as
the Run-time error seems to have resulted from
my renaming earnings.xls into something else.
The Yahoo downloading is OK while the Zacks data shows "7/27/2013" for all stocks.
In my own test, zacks dates are working correctly. Make sure that you have downloaded the most recent version of earnings.xls.
when I input alot of stocks (200) i get a runtime error ''-2147352319 (8002010)'
Any thoughts?
Ideally i woudl like to be able to update this list 1 time a day but will settle for 1 time a week with 430 stocks.
Please let me know, thanks
Not sure if my comment got deleted or what but I am unable to use this program when i have more than 20 stocks in the file... any thoughts?
I also get this error now of "Microsoft Excel is waiting for another application to complete an OLE action"
Pleaes help.
Hi Neil,
Firstly, thank you for your downloader.
I want to use it from an Excel VBA script. How can I do it?
Thanks again.
Still trying to get this to work.
How can i get it to open explorer 64 bit instead of 32. and or google chrome?