Question description:
This user has given permission to use the problem statement for this
blog.
i need a formula to calculate previous day intraday high from google finance to google sheet.
I AM USING THIS ONE
=GOOGLEFINANCE(A2,"high",today()-1)
BUT ITS NOT WORKING AND GIVING ME ERROR
"When evaluating GOOGLEFINANCE, the query for the symbol: 'HDFCBANK' returned no data."
HERE A2 IS "HDFCBANK" A TICKER..
AND I ALSO DONT WANT DATE COLUMN WITH THE PREVIOUS DAY INTRADAY HIGH PRICE.
Solved by G. E. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
02/04/2018 - 10:03
Hello! Welcome to Excel chat.
User
02/04/2018 - 10:03
hello
User
02/04/2018 - 10:04
can you solve my problem?
Excelchat Expert
02/04/2018 - 10:04
I am looking at it. Just give me few moments please.
User
02/04/2018 - 10:04
okay
Excelchat Expert
02/04/2018 - 10:05
Please see the formula now.
Excelchat Expert
02/04/2018 - 10:06
You have to specify a start and an end date.
User
02/04/2018 - 10:06
idont need date and high with this price
User
02/04/2018 - 10:06
i only need value
Excelchat Expert
02/04/2018 - 10:06
Ok.
Excelchat Expert
02/04/2018 - 10:09
Well, that's not possible for historical data.
Excelchat Expert
02/04/2018 - 10:09
Even if it's for a single day.
Excelchat Expert
02/04/2018 - 10:09
Google doesn't support that.
Excelchat Expert
02/04/2018 - 10:10
This is as per from Google support: "Realtime results will be returned as a value within a single cell. Historical data, even for a single day, will be returned as an expanded array with column headers."
Excelchat Expert
02/04/2018 - 10:10
In that case as a workaround, you can hide the date column.
User
02/04/2018 - 10:11
i was manged to do that but for 6 days back, i want to do it for previous day.
User
02/04/2018 - 10:11
et me share formula with you which i used
Excelchat Expert
02/04/2018 - 10:11
Sure.
User
02/04/2018 - 10:11
=INDEX(GOOGLEFINANCE(A2,"high",today()-6,-1),2,2,1)
User
02/04/2018 - 10:12
it gives me value of 6 days back intraday high
Excelchat Expert
02/04/2018 - 10:12
Ok.
Excelchat Expert
02/04/2018 - 10:12
Formula is correct. And it works.
User
02/04/2018 - 10:13
but i need it for 1 day back
Excelchat Expert
02/04/2018 - 10:13
Ok. You are not getting that value, because today() - 1 is Sunday and there is no value for that.
Excelchat Expert
02/04/2018 - 10:14
Now if you use the same formula tomorrow, it will work.
User
02/04/2018 - 10:14
today()-1 i understand is sunday, beyond 6 if you try any number it works and there were sundays before
Excelchat Expert
02/04/2018 - 10:14
Let's try some days/
User
02/04/2018 - 10:15
sure
User
02/04/2018 - 10:16
try last to last week sunday
User
02/04/2018 - 10:16
will show you the value
User
02/04/2018 - 10:17
today()-8 try this
User
02/04/2018 - 10:17
it was sunday that day
Excelchat Expert
02/04/2018 - 10:17
Yes, got it.
User
02/04/2018 - 10:17
yeah
Excelchat Expert
02/04/2018 - 10:18
So I am just wondering if this has something to do with financial year.
Excelchat Expert
02/04/2018 - 10:18
Because in India, all previous 4 or 5 days were holidays.
User
02/04/2018 - 10:19
yes
Excelchat Expert
02/04/2018 - 10:19
And now this is a new financial year.
Excelchat Expert
02/04/2018 - 10:19
Ok. Let's see if this works on previous financial year.
User
02/04/2018 - 10:19
market in india just closed 5 minutes ago
Excelchat Expert
02/04/2018 - 10:19
Ok.
Excelchat Expert
02/04/2018 - 10:20
For today, that's working.
User
02/04/2018 - 10:21
where?
User
02/04/2018 - 10:21
what formula?
Excelchat Expert
02/04/2018 - 10:21
A3
Excelchat Expert
02/04/2018 - 10:22
Without today, for current data, you don't need to specify the date.
User
02/04/2018 - 10:22
can you paste the formula
User
02/04/2018 - 10:22
dont understand
Excelchat Expert
02/04/2018 - 10:22
=GOOGLEFINANCE("HDFC", "high")
Excelchat Expert
02/04/2018 - 10:23
For current prices, you don't need to specify today().
Excelchat Expert
02/04/2018 - 10:23
This will give today's high price.
User
02/04/2018 - 10:23
this i know
User
02/04/2018 - 10:23
i want to know for previous trading day high
Excelchat Expert
02/04/2018 - 10:24
Then your formula is correct.
Excelchat Expert
02/04/2018 - 10:24
For some reasons' it's not working.
Excelchat Expert
02/04/2018 - 10:24
=INDEX(GOOGLEFINANCE($A$2,"high",today()-1,-1),2,2,1)
Excelchat Expert
02/04/2018 - 10:24
This should work tomorrow.
User
02/04/2018 - 10:24
that is why i am talking to you
Excelchat Expert
02/04/2018 - 10:25
I understand, but I have shared my best knowledge to you and I am still exploring it further.
Excelchat Expert
02/04/2018 - 10:25
My understanding was that it doesn't work for holidays.
Excelchat Expert
02/04/2018 - 10:25
And here this has additional complexity of new FY.
User
02/04/2018 - 10:26
okay
User
02/04/2018 - 10:29
ARE YOU THERE?
Excelchat Expert
02/04/2018 - 10:29
Yeah, I am here, checking previous year results.
Excelchat Expert
02/04/2018 - 10:30
There is a possibility that it doesn't consider previous intraday high of more than 2 days.
Excelchat Expert
02/04/2018 - 10:30
Just give me few moments.
User
02/04/2018 - 10:30
OKAYY
Excelchat Expert
02/04/2018 - 10:32
Yeah, got it.
Excelchat Expert
02/04/2018 - 10:33
Last year, Good Friday was on 14th April. Hence that becomes 3 continuous holidays. 14, 15, 16.
Excelchat Expert
02/04/2018 - 10:33
So today()-350 is 17 April 2017, that's Monday.
Excelchat Expert
02/04/2018 - 10:34
I am sorry.
Excelchat Expert
02/04/2018 - 10:34
That's giving some results for sure.
User
02/04/2018 - 10:35
WHY ARE YOU SUBTRACTING 350?
Excelchat Expert
02/04/2018 - 10:38
That was to get 17 april 2017 date.
Excelchat Expert
02/04/2018 - 10:38
Anyway, that's not right. My assumption was that it's not considering that long holiday break while calculating the previous intraday price.
User
02/04/2018 - 10:39
OKAY
User
02/04/2018 - 10:39
I SHOULD WAIT FOR TOMMOROW
Excelchat Expert
02/04/2018 - 10:39
Yes. That's what I am expecting too.
User
02/04/2018 - 10:39
OKAY THNKS
Excelchat Expert
02/04/2018 - 10:40
I hope this will work out for you tomorrow.
User
02/04/2018 - 10:40
LIKEWAISE
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
