Join Early Retirement Today
Reply
 
Thread Tools Search this Thread Display Modes
Formula to Retrieve Earnings Date
Old 07-17-2020, 05:54 PM   #1
Confused about dryer sheets
 
Join Date: Jul 2020
Posts: 5
Formula to Retrieve Earnings Date

I am requesting help in putting together an “=ImportHTML” formula for Google Sheets to get the “Earnings Date” for ticker symbol EA (or any other valid ticker symbol) from CNBC (https://www.cnbc.com/quotes/?symbol=EA&qsearchterm=). In this case, I am trying to import the date “2020-07-30.”


The formula, as I understand it, will be similar to the one below:


=IMPORThtML("http://www.cnbc.com/quotes/?symbol=ea&qsearchterm="& A4,"//table/tbody[1]/tr[3]/td[1]")


My time and effort has yet to yield results. Suggestions will be greatly appreciated.
__________________

Crusader is offline   Reply With Quote
Join the #1 Early Retirement and Financial Independence Forum Today - It's Totally Free!

Are you planning to be financially independent as early as possible so you can live life on your own terms? Discuss successful investing strategies, asset allocation models, tax strategies and other related topics in our online forum community. Our members range from young folks just starting their journey to financial independence, military retirees and even multimillionaires. No matter where you fit in you'll find that Early-Retirement.org is a great community to join. Best of all it's totally FREE!

You are currently viewing our boards as a guest so you have limited access to our community. Please take the time to register and you will gain a lot of great new features including; the ability to participate in discussions, network with our members, see fewer ads, upload photographs, create a retirement blog, send private messages and so much, much more!

Old 07-18-2020, 08:08 AM   #2
Recycles dryer sheets
 
Join Date: Feb 2014
Location: Pittsburgh
Posts: 68
Quote:
Originally Posted by Crusader View Post
I am requesting help in putting together an “=ImportHTML” formula for Google Sheets to get the “Earnings Date” for ticker symbol EA (or any other valid ticker symbol) from CNBC (https://www.cnbc.com/quotes/?symbol=EA&qsearchterm=). In this case, I am trying to import the date “2020-07-30.”


The formula, as I understand it, will be similar to the one below:


=IMPORThtML("http://www.cnbc.com/quotes/?symbol=ea&qsearchterm="& A4,"//table/tbody[1]/tr[3]/td[1]")


My time and effort has yet to yield results. Suggestions will be greatly appreciated.
I'm no expert on this Sheets function, but based on a little reading and testing, it looks like your syntax should be more like (and this still won't work as you desire, but it does retrieve data from the website)...

=IMPORThtML("http://www.cnbc.com/quotes/?symbol=ea", "TABLE", 1)

"TABLE" must be explicitly used as a parameter assuming you wish to retrieve a table of data. If you wish to retrieve a list from the website you would use "LIST" as the parameter.

The third parameter must be a number. If you wish it to be a derived number, you would have to derive it first in a different cell of the spreadsheet and then reference the cell in the formula.

I found data from that website using tables 1 and 5 only. Tables 2, 3, and 4 appear to be empty ones. Anything above 5 don't seem to be valid tables on the page (they give an error).

I suspect they are doing fancy stuff on the website that makes it difficult/impossible to retrieve all the data on it. For example, I found reference that you can only retrieve data from how the webpage first displays on loading. It's possible that some of the data on that page is rendered in a secondary call to some data source.

Again, no expert here. But that's my two cents since no expert has yet to chime in with an answer.
__________________

PaunchyPirate is offline   Reply With Quote
Old 07-18-2020, 09:46 AM   #3
Thinks s/he gets paid by the post
 
Join Date: Mar 2013
Location: Coronado
Posts: 1,831
This is not going to work the way you're trying to do it. If you select "view source" in your browser, you'll see that the actual data you want is in a JSON string like this:

Quote:
var symbolInfo = {"symbol":"EA","curmktstatus":"POST_MKT","mappedSy mbol":{"xsi:nil":"true"},"source":"Last NASDAQ LS, VOL From CTA","EventData":{"yrhiind":"N","announce_time":"A ","next_earnings_date_today":"N","yrloind":"N" ,"next_earnings_date":"2020-07-30","is_halted":"N"},...
When your browser loads the page, it executes some JavaScript code that parses that JSON string and inserts the data elements into the tables you see.

When Google Sheets pulls that page with IMPORTHTML, it gets the raw HTML and it doesn't run the Javascript to recreate the tables. So when you ask it to retrieve the table you want, it can only get the placeholders, which are empty.
cathy63 is offline   Reply With Quote
Old 07-18-2020, 01:41 PM   #4
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
target2019's Avatar
 
Join Date: Dec 2008
Location: Stuck in the mud somewhere in the swamps of Jersey
Posts: 6,297
Look for the data on a site using HTML tables? I think Cathy has posted in another thread a solution that was similar.
target2019 is offline   Reply With Quote
Old 07-18-2020, 01:51 PM   #5
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
target2019's Avatar
 
Join Date: Dec 2008
Location: Stuck in the mud somewhere in the swamps of Jersey
Posts: 6,297
This thread is about screen scraping similar data from the Morningstar site:
https://www.early-retirement.org/for...ytd-94768.html
target2019 is offline   Reply With Quote
Old 07-18-2020, 02:21 PM   #6
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
donheff's Avatar
 
Join Date: Feb 2006
Location: Washington, DC
Posts: 10,007
I have forgotten everything I learned in that thread. Now I just beg Cathy61 to solve my problems.
__________________
Every man is, or hopes to be, an Idler. -- Samuel Johnson
donheff is offline   Reply With Quote
Old 07-18-2020, 03:32 PM   #7
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
target2019's Avatar
 
Join Date: Dec 2008
Location: Stuck in the mud somewhere in the swamps of Jersey
Posts: 6,297
Over the years I've found and used many nifty tricks with spreadsheets. Then I forget them...
target2019 is offline   Reply With Quote
Formula to Retrieve Earnings Date
Old 07-18-2020, 04:05 PM   #8
Confused about dryer sheets
 
Join Date: Jul 2020
Posts: 5
Formula to Retrieve Earnings Date

Thank you everyone for support/suggestions.

I am totally with cathy63: pulling this information the "standard" way will not work - JSON technology has made sure of that. I looked at the link posted by target2019 for scraping similar data from Morningstar; converting the formula from "returnytd" to earnings date is way beyond my skill level - but I will look into it. Until then, my manual operation lives on.

I am open to using Morningstar or another reliable website to get the information I need.
Crusader is offline   Reply With Quote
Old 07-18-2020, 04:09 PM   #9
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
target2019's Avatar
 
Join Date: Dec 2008
Location: Stuck in the mud somewhere in the swamps of Jersey
Posts: 6,297
Crusader,
I could not find the earnings date on those Morningstar pages.

However, this page (and Yahoo) have the date.
https://money.cnn.com/quote/quote.html?symb=EA

I am just not savvy enough to create the importHTML or importXML statement.

I can, however, easily get an error. LOL.
target2019 is offline   Reply With Quote
Formula to Retrieve Earnings Date
Old 07-18-2020, 04:34 PM   #10
Confused about dryer sheets
 
Join Date: Jul 2020
Posts: 5
Formula to Retrieve Earnings Date

Thank you, target2019.

I am not skilled enough to put together a complex importHTML formula; however, looks like I have a better chance of a formula with https://money.cnn.com.

Will share the formula - if I come up with one. Until then, please keep the suggestions coming.
Crusader is offline   Reply With Quote
Old 07-18-2020, 06:46 PM   #11
Thinks s/he gets paid by the post
 
Join Date: Mar 2013
Location: Coronado
Posts: 1,831
Quote:
Originally Posted by target2019 View Post
Crusader,
I could not find the earnings date on those Morningstar pages.

However, this page (and Yahoo) have the date.
https://money.cnn.com/quote/quote.html?symb=EA

I am just not savvy enough to create the importHTML or importXML statement.

I can, however, easily get an error. LOL.
The Yahoo Finance page has the same problem as the CNBC page where the data is loaded from JSON. But, the CNN page works! Here's a function that returns the date.

=importxml("https://money.cnn.com/quote/quote.html?symb=EA","//*[@id='wsod_snapshotView']/div[5]/div[2]/div/table/tr[1]/td[2]")

I have no idea why CNN has the earnings report set for one day later than everyone else, since it appears the report will actually be after market close on 7/29, but hopefully Crusader doesn't need exact accuracy on this. Thanks for suggesting these other pages target2019.

(If copy/paste of the above text does not work, it may be due to your browser suppressing some characters when displaying code in forum posts, which has been a problem in the past. Refer to the image below for the exact function.)
Attached Images
File Type: jpg Annotation 2020-07-18 173511.jpg (27.9 KB, 16 views)
cathy63 is offline   Reply With Quote
Formula to Retrieve Earnings Date
Old 07-19-2020, 11:25 AM   #12
Confused about dryer sheets
 
Join Date: Jul 2020
Posts: 5
Formula to Retrieve Earnings Date

Thank you and God Bless You, cathy63: your formula worked!

Also thanks to target2019 and others for suggestions and support.

I consider this post "resolved." Since I am not familiar with this forum, I don't know if I can mark it as such.
Crusader is offline   Reply With Quote
Old 07-19-2020, 01:17 PM   #13
Thinks s/he gets paid by the post
 
Join Date: Mar 2013
Location: Coronado
Posts: 1,831
Quote:
Originally Posted by Crusader View Post
Thank you and God Bless You, cathy63: your formula worked!

Also thanks to target2019 and others for suggestions and support.

I consider this post "resolved." Since I am not familiar with this forum, I don't know if I can mark it as such.
You're welcome, I'm happy to have helped! This is more of a discussion forum, rather than a tech support site, so we don't have a "resolved" button or anything like that.

Now that you've found us, I hope you'll stick around, introduce yourself, and explore more of the discussions about early retirement.
cathy63 is offline   Reply With Quote
Old 07-19-2020, 03:46 PM   #14
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
target2019's Avatar
 
Join Date: Dec 2008
Location: Stuck in the mud somewhere in the swamps of Jersey
Posts: 6,297
This is a great way to end the weekend, after picking away at the various import functions in google sheets. I was able to add yet one more cathy63 function to my finance functions worksheet.

One thing I found was that browsers have an "Inspect" command. Right-click on the web page somewhere and you'll see it.

It can be frustrating looking through the developer tools. But I found the element in that source which cathy63 is referencing. Of course her function works, but I am confused by the following.

Her xpath (this is the 2nd part of her importXML function):
Code:
//*[@id='wsod_snapshotView']/div[5]/div[2]/div/table/tr[1]/td[2]
Brave browser - "copy xpath" while selecting the earnings date in the source:
Code:
//*[@id="wsod_snapshotView"]/div[4]/div[2]/div/table/tbody/tr[1]/td[2]
I get why a single quote is used instead of double quote.
I understand that tbody isn't necessary.

But what of the "div[4]"? The working function uses "div[5]"...
target2019 is offline   Reply With Quote
Old 07-19-2020, 04:22 PM   #15
Thinks s/he gets paid by the post
 
Join Date: Mar 2013
Location: Coronado
Posts: 1,831
Quote:
Originally Posted by target2019 View Post
This is a great way to end the weekend, after picking away at the various import functions in google sheets. I was able to add yet one more cathy63 function to my finance functions worksheet.

One thing I found was that browsers have an "Inspect" command. Right-click on the web page somewhere and you'll see it.

It can be frustrating looking through the developer tools. But I found the element in that source which cathy63 is referencing. Of course her function works, but I am confused by the following.

Her xpath (this is the 2nd part of her importXML function):
Code:
//*[@id='wsod_snapshotView']/div[5]/div[2]/div/table/tr[1]/td[2]
Brave browser - "copy xpath" while selecting the earnings date in the source:
Code:
//*[@id="wsod_snapshotView"]/div[4]/div[2]/div/table/tbody/tr[1]/td[2]
I get why a single quote is used instead of double quote.
I understand that tbody isn't necessary.

But what of the "div[4]"? The working function uses "div[5]"...
I got stuck on this same issue yesterday when I was working on it. I used Chrome's Dev tools to grab the Xpath and I got the same one you did, with div[4], and that didn't work, which was puzzling because looking at the HTML it seemed correct.

To debug it, I tried this "//*[@id='wsod_snapshotView']/div" which returns all the divs under the selected id. Now I could see that the data I wanted was actually in the second column of row 5, so once I had that it wasn't too hard to build a working Xpath query with div[5] instead of [4].

My theory about why this happens is that the ImportXML function is actually executing on Google's servers, not in the browser that's running Google Sheets. So some Google server is sending a GET request to the server at https://money.cnn.com and I suspect the cnn.com server has some logic that formats the response a little differently for each browser type. The Google server probably doesn't identify itself as Chrome, so it doesn't get back exactly what we see in the dev tools window.
cathy63 is offline   Reply With Quote
Old 07-19-2020, 05:41 PM   #16
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
target2019's Avatar
 
Join Date: Dec 2008
Location: Stuck in the mud somewhere in the swamps of Jersey
Posts: 6,297
This also works.
Code:
=importxml("https://money.cnn.com/quote/quote.html?symb=EA","//*[@class='wsod_dataTable wsod_dataTableBig wsod_quoteFinancialsModule']/tr[1]/td[2]")
Have to be more careful using @class, though.

To continue with the question of why div[5] vs. div[4], I think it could be that once the selector (//) finds the string, the Google importXML function counts end tags (/div). But the 'copy xpath' feature in browser inspector is counting opening tags (div).

In any event, we are all good.
target2019 is offline   Reply With Quote
Old 07-19-2020, 05:47 PM   #17
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
donheff's Avatar
 
Join Date: Feb 2006
Location: Washington, DC
Posts: 10,007
Damn, you guys are dragging me back into the Chrome Developer Tools again.
__________________
Every man is, or hopes to be, an Idler. -- Samuel Johnson
donheff is offline   Reply With Quote
Old 07-21-2020, 01:02 PM   #18
Confused about dryer sheets
 
Join Date: Jul 2020
Posts: 5
Quote:
Originally Posted by cathy63 View Post
Now that you've found us, I hope you'll stick around, introduce yourself, and explore more of the discussions about early retirement.
Thank you cathy63; I plan to visit often and learn.
Crusader is offline   Reply With Quote
Old 08-01-2020, 12:01 PM   #19
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
target2019's Avatar
 
Join Date: Dec 2008
Location: Stuck in the mud somewhere in the swamps of Jersey
Posts: 6,297
I found another working formula. Unfortunately I lost the web page that had a neat description of ETF info and how to fetch YTD performance from the site below.
Code:
=index(IMPORTXML("http://www.etf.com/AOR/table","//span[@class='cell col4 perfYtd']"),1,1)
You'll have to change out AOR for your ETF symbol. If you go to the web page source, it is easy enough to find other information in tables on the page.

One error I ran into was not seeing the capital 'Y' in that formula. So be careful with any changes you make.

Here's 1-year performance:
Code:
=index(IMPORTXML("http://www.etf.com/AOR/table","//span[@class='cell col5 perf1Yr']"),1,1)
__________________

target2019 is offline   Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Results when changing SS start date or Pension start date Clone FIRECalc support 5 06-15-2020 08:10 AM
% of Lifetime Earnings Saved based on SSA Medicare Earnings REattempt FIRE and Money 17 08-01-2013 06:51 PM
Remember Date You Retired?...Know Date You Will Retire? Danny Other topics 35 07-09-2007 12:02 PM

» Quick Links

 
All times are GMT -6. The time now is 11:39 PM.
 
Powered by vBulletin® Version 3.8.8 Beta 1
Copyright ©2000 - 2020, vBulletin Solutions, Inc.
×