0

Can someone help me write a one cell query similar to the one below but using yahoo finance data instead of google finance. This sparkline shows a trend on daily price for the last 365 days and colors the sparkline green or red depending if the current price is higher or lower than the first day from the range provided. In this case 1st day from 365 days.

I really hope this can be done as I don't want to create additional tables or tabs to store data and then produce a sparkline.

=sparkline(query(googlefinance(AAPL, "price", today()-365, today()-2), "select Col2 label Col2 ''", 1),{"color",if( INDEX(googlefinance(AAPL,"price",today()-365),2,2) < INDEX(googlefinance(AAPL, "price", today()-2),2,2),"green","red");"linewidth",2})

Here is an example of how to grab the latest price using yahoo finance.

=index(IMPORTXML("https://finance.yahoo.com/quote/AAPL","//*[@id='quote-header-info']//span"),2)
player0
  • 124,011
  • 12
  • 67
  • 124
domid
  • 33
  • 7

1 Answers1

2

try:

=SPARKLINE(QUERY(IMPORTHTML(
 "https://finance.yahoo.com/quote/AAPL/history?period1="&86400*(
 TODAY()-365)-2209161600&"&period2="&86400*TODAY()-2209161600&
 "&interval=1d&filter=history&frequency=1d",
 "table", 1),
 "select Col5 order by Col1 asc offset 1", 0), {"color", IF(INDEX(SORT(IMPORTHTML(
 "https://finance.yahoo.com/quote/AAPL/history?period1="&86400*(
 TODAY()-365)-2209161600&"&period2="&86400*TODAY()-2209161600&
 "&interval=1d&filter=history&frequency=1d", 
 "table", 1), 1, 1), 1, 5) < INDEX(IMPORTXML(
 "https://finance.yahoo.com/quote/AAPL", 
 "//*[@id='quote-header-info']//span"), 2),
 "green", "red"); "linewidth", 2})

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • Thanks this looks great except its flipped. How would I get it flipped on the x axis? – domid Feb 29 '20 at 16:18
  • Also if possible can you provide an example how I can change the time frame to say 30 days. The timestamps I have a hard time understanding. Thank you! – domid Feb 29 '20 at 16:19
  • @domid answer updated. for 30d you just change 365 to 30 – player0 Feb 29 '20 at 16:24
  • Thank you very much for your help. Unfortunately yahoo finance it seems doesn't provide historical data past a certain period. Top row is google finance. Bottom yahoo finance. https://pasteboard.co/IWX2wWc.png Would you be able to help me build the same query using the free . API https://www.alphavantage.co/documentation/ ? Free API keys https://www.alphavantage.co/support/#api-key – domid Feb 29 '20 at 16:59
  • Actually it looks like because you're using the IMPORTHTML function yahoo may not be providing all the data as it seems to be loading the rest on scroll. Is there another way to force the entire 365 day data into the spreadsheet? Maybe a csv or json? – domid Feb 29 '20 at 18:57
  • 1
    Hi , please accept the answer of @player0 if it did work for you and if you want to go further with more questions please ask them as different questions and not in the comment section (i.e, don't ask for more functionalities in the comments, post a question instead). This is to respect the Stack Overflow rules. Thanks in advanced :) – Mateo Randwolf Mar 03 '20 at 09:51