Calculating the 50 days high /low of a stock with help of googlesheets

in my last post we started to build a Stock Watchlist with help of googlesheets. Now we go one step further and will calculate the 50 days high (or low) of a given stock. Furthermore we would like to set an automatic flag whenever the actual quote reach the 50 days high (or low). This flag can be evaluated later for instance within a dashboard.

The end product looks like this:

The 50 Days High is calculated using a combination between the QUERY and the GOOGLEFINANCE function:

=query(GoogleFinance($A5,"close",today()-$H$2,today()),"select max(Col2) label max(Col2) ''",1)

 In this expression, the value stored in the cell H2 contains the Nr of calendar days in the past to reach the 50 days.

There is a small problem to solve here. Googlefinance returns an array containing trading days which are not the same as calendar days. Hoy many days we need to go in the past to reach 50 trading days may vary from day to day. For instance, the calculation shown here was done using a value of H2 = 69 calendar days.

Coming back to our expression above:

The googlefinance function look for closing prices between today and 69 calendar days in the past. Now we need just to create a "Flag" that returns TRUE/FALSE in the case the current quote corresponds to a 50 Days high or not. =if(H5>I5,TRUE,FALSE) Where H5 is our Quote and I5 the calculated 50 Days High

The Query statement use an array returned by the Googlefinance Function and perform a select statement on that array, selecting the maximum value (in order to obtain the low, just replace max(Col2) by min(Col2) and change the label correspondingly)

Now we have an automatic detection of 50 Days high which can be based on a stock list. There is some calculation time required and by large lists the system will take a while, but it s acceptable.