Formula to Import Earnings Date

Discuss the spreadsheet application
Post Reply
crusader
Volunteer
Posts: 505
Joined: Sun Jan 20, 2008 5:06 am

Formula to Import Earnings Date

Post by crusader »

For quite a while I had been using the formula below to import "Earnings Date" from CNBC (https://www.cnbc.com/quotes/). For the past few months, the formula has stopped working. My understanding is, elements have been moved around on the page (redesigned) and I am no longer correctly "pointing" to the element I wish to import. I don't know HTML to figure out how to correct the formula so it points to "Earnings Date" (under "Events") on the "redesigned" web page. All my trial and error efforts have been in vain.

Code: Select all

=if(A4="","",(iferror(IMPORTXML("https://www.cnbc.com/quotes/"&A4,"//html/body/div[2]/div/div[1]/div[3]/div/div[2]/div[1]/div[5]/div[2]/section/div[3]/ul/li[1]/span[2]"),"")))
In the above example, "A4" has the ticker symbol "AAL" (without the quotes) for which I wish to import Earnings Date .

I am using LO Calc 7.4.2.3 (x64)

All help will be greatly appreciated!
LO 7.x on Windows 10
A candle loses nothing by lighting another candle.
User avatar
Zizi64
Volunteer
Posts: 10804
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Formula to Import Earnings Date

Post by Zizi64 »

Code: Select all

=if(A4="","",(iferror(IMPORTXML("https://www.cnbc.com/quotes/"&A4,"//html/body/div[2]/div/div[1]/div[3]/div/div[2]/div[1]/div[5]/div[2]/section/div[3]/ul/li[1]/span[2]"),"")))
What extension are you using in your office suite? The function IMPORTXML() not exist by default in my 7.4.1 version Calc application.

I just googled it: it is a GoogleSheets function.
Tibor Kovacs, Hungary; LO6.1.6, 6.4.7 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.3.5;AOO4.1.13
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
crusader
Volunteer
Posts: 505
Joined: Sun Jan 20, 2008 5:06 am

Re: Formula to Import Earnings Date

Post by crusader »

Zizi64, thank you for looking into it - and you are right on the money: this is a GoogleSheets function; however, it worked seamlessly with LO Calc until CNBC implemented (seemingly minor) changes to the web page. As I understand it, the key is correctly "pointing" to the element I need to import from the web page (code below). If I am not mistaken, that is what this issue is all about.

Code: Select all

"//html/body/div[2]/div/div[1]/div[3]/div/div[2]/div[1]/div[5]/div[2]/section/div[3]/ul/li[1]/span[2]"
LO 7.x on Windows 10
A candle loses nothing by lighting another candle.
User avatar
Villeroy
Volunteer
Posts: 30885
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Formula to Import Earnings Date

Post by Villeroy »

The source code of https://www.cnbc.com/quotes/AAL contains nothing but JavaScript. The page is calculated entirely in your browser.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
crusader
Volunteer
Posts: 505
Joined: Sun Jan 20, 2008 5:06 am

Re: Formula to Import Earnings Date

Post by crusader »

Villeroy, you are right.

A correction, please: I meant to say the data from GoogleSheets imported seamlessly into LO. The primary place this formula was/is used is Googlesheets; LO then imports the data from Googlesheets. All this worked fine until GoogleSheets couldn't get the Earnings Date.

GoogleSheets has a good number of built in formulas for financial needs (stock prices, EPS, etc.); however there is no built in formula for Earnings Date.

I am hoping down the road LO will have a feature similar to Excel (I am not a M$ buff) which allows it to get financial data.
LO 7.x on Windows 10
A candle loses nothing by lighting another candle.
User avatar
Villeroy
Volunteer
Posts: 30885
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Formula to Import Earnings Date

Post by Villeroy »

Microsoft, Google and Apple are the richest companies on the planet. Their spreadsheet programs Excel, Google Sheets and Numbers can easily query these data from their company owned servers.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply