I am trying to use blew expression in Google SpreadSheet.
=min(GoogleFinance("HKDUSD","price",date(A2)-1,date(A2)))
But since A2 data is in mm/dd/year format, we need to convert it to yy,mm,dd format as parameter of date().Does Google SpeadSheet has some API to do such convert?
It actually works if you just add the dates as they are without the date function wrapped around them
=min(GoogleFinance("HKDUSD","price",L1-1,L1))
Edited: To answer your follow up question about the date format - a formulaic workaround you do uses regex replace and basically just swaps the two capture groups in month and day with this regex "(\w+)/(\w+)":
=min(GoogleFinance("HKDUSD","price",REGEXREPLACE(L1,"(\w+)/(\w+)","$2/$1")-1,REGEXREPLACE(L1,"(\w+)/(\w+)","$2/$1")))
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments