0

I'm looking to run a query in GoogleSheets using the GOOGLEFINANCE function to pull down a list of stocks reaching new 26-week highs (limited to US companies or perhaps more simply US stock markets).

A previous question resolved the issue of computing a specified stock's 26-week high price. I'm seeking to create lists throughout the day of stocks creating new 26-week highs for the day.

I've looked for an ImportHTML function solution but Google Finance Stock Screener does not have produce such lists.

Can anyone advise how to create such a stock-screen query or any other function in GoogleSheets using Google Finance's data to get the results?

  • I think that the core of your question is about asking for recommendations for a real-time stock data source. If this is right this question is off-topic here and it should be reworded and posted to Quantivative Finance – Rubén Aug 2 '15 at 14:34
  • @Rubén Google Finance already reports trading prices in real-time for US stocks. The "real-time" reference was made to avoid confusion with alternate sources, such as WSJ.com that publish 52-week data hourly. The reference is now removed. The question in short is how to make a stock screener in GoogleSheets, preferably using Google Finance's data. – P.D. Aug 2 '15 at 15:05
1

The GoogleFinance command retrieves prices for a particular stock, specified as the first argument of the command. In order to receive data for multiple stocks, you need multiple GoogleFinance commands, one for each stock.

Possible implementation:

  • Column A lists the stocks: GOOG, APPL, MSFT, etc.
  • Column B lists today's high, like B2 =GOOGLEFINANCE(A2,"high")
  • Column C lists 26-week high (as explained in my answer; the last option from there should be used to get a single-cell result).

Then, on another sheet, you put

=Query(Sheet1!A:C,"select * where B=C") 

or (to get only the stock symbols without prices)

=Query(Sheet1!A:C,"select A where B=C") 

Notes

  • You'll have to obtain and enter (copy/paste) the list of stocks yourself.
  • This is a data-intensive process, especially when many stocks are involved. You will likely find the spreadsheet sluggish, or will encounter an error when a Google server decides you are using too much of their resources.
  • A spreadsheet is not a trader's dashboard and should not be used as one.
  • Thanks. Google Finance has a disclaimer per your note. I'm looking to create the filtered list from the universe of US companies of the day's stocks making new 26-week highs. I understand from web searches that this sort of stock screen was possible before the Google Finance-specific APIs were made defunct (developers.google.com/finance). Do you know if there is a way to create Google Finance broad market stock screens using Google Sheets? – P.D. Aug 2 '15 at 19:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.