here is how I did it, for those who wants something similar but have rusty programming skills.
Cell A7 formula looking for a value 2 months old:
The value is displayed in “B8”. Note that the values collected during the weekend are shown by googlefinance with N/a .
On the same column the same formula is repeated changing how far in the past I want to check the price.
C8 shows the difference in percentage compared with today’s value . In case the price is collected during the weekend I had to force the cell to show show “0” instead of an empty one using the IF formula:
D8 : threshold check for value in cell C8: if the value exceeds the threshold defined in E8 (-15% in my case), then the cell value will be 1, otherwise 0
F8: cell used to trigger the email message in case one of the values in column D is 1 (which means one threshold crossed somewhere) . If column D has a cell with one of the value se t to 1 (which means threshold crossed) F8 will be set to 1 as well.
Finally, the email script will check the value in F8 and send a message if the value is 1