A Real-Time Watchlist With Automated Email Triggers - Tracking And Modelling Stock Dividend Portfolios In Google Sheets - Part 5

by: Stefan Redlich

Summary

Part 4 now introduces a real-time watchlist with automated email triggers (checked every minute).

Users will get automatic email updates once the target price has been reached with specific information.

By default, you will get no duplicate mails.

Please give me feedback regarding this and what else you are wishing for in this series.

With Part 5 of this series, the spreadsheet is enhanced by featuring a real-time watchlist with automated email notifications. This means, you will get an email with a customized message as soon as a stock on your watchlist reaches the target price you have defined. This helps you in making timely decisions when, for whatever reason, investment opportunities arise. You can grab them without having to constantly monitor stock prices.

If you are not familiar with all the other features the spreadsheet offers in this version, make sure to check out the latest articles (here and here).

Click to enlarge

Now, let's get started!

Without further ado, here is the link to the most current version of our Google Sheet.

Here is the email output...

Click to enlarge

Every minute (current setting), it is checked whether the defined target price has been reached and if it has indeed, you will receive an email which looks as depicted. You will not get any duplicate emails by default.

The email contains information on which stock from your watchlist has reached the target price, when this happened as well as information on the target price and the current price. You can push these mails to your Smartphone, Smartwatch, etc., and thus know instantly when you should heat up that "buy finger" without having to constantly follow all the individual stock prices.

And here is the newest version of the spreadsheet...

I do not want to bother you with the technique behind so let's jump into the actual watchlist spreadsheet. In its current form this looks as follows:

Click to enlarge

As most of the columns are self-explanatory and also include comments in the header line, I will only mention a few general things and specifics:

  • The yellow columns are the input columns for the email address you want the notification to be sent, the stock itself and the target price. The rest is calculated automatically. One important comment on the email address: Please only enter an address here that is yours or belongs to someone you know who wants to receive that information and not for any other purposes as emails are sent instantly and depending on the number of stocks can easily spam people's accounts.
  • Column B includes the actual "email text" (which you can adjust as you want) which right now looks like this:

The buy trigger threshold for JPM has been reached. Current price is 67.52 USD, target price is 70 USD.

  • Column C is locked for editing by default as the "yes" indicator is used to determine for which stock an email should be sent.
  • Column I shows you the dividend yield if your stock reaches its target price. In a later version, I am planning to include stock count and income calculation here as well.
  • The last column is colored grey as it is populated with values via script and indicates if an email has been sent. This also protects you from receiving hundreds of email notifications for a "triggered" stock.

How to use the last column?

By default, you will only get one email per stock if the target price has been reached. Simultaneously, with the incoming mail, the respective cell in the sheet will be populated with "EMAIL_SENT." If you now want to change the target price and lower it further or for whatever other reason want to receive a second mail, you simple have to delete the respective cell.

In case there is interest, I could make another version of the spreadsheet where this default setting is overridden and you will receive an unlimited number of emails or only a certain number.

Some final remarks and a call for action

  • First of all, I really hope that you find this useful and helpful.
  • Secondly, I have invested a lot of time and energy into this and thus if you want to share this on your platforms or so, you are welcome to do so but please give me some kudos and keep this open source for the time being.
  • Thirdly, I actually have hardly any coding experience and thus if you encounter any bugs or so, please let me know. Also, please do not rearrange the order of the columns or insert any columns in between as this will likely break the email script.
  • Last but not least: Let me know in the comments section if you find this helpful or encountered any difficulties with the feature.

Please "follow me" if you want to read more about this and let me know what other features you are missing. Maybe a dividend shot clock or a dividend projection model or some tweaks to the feature discussed here? I am curious to find out.

Disclosure: I am/we are long KO, JPM, PG.

I wrote this article myself, and it expresses my own opinions. I am not receiving compensation for it (other than from Seeking Alpha). I have no business relationship with any company whose stock is mentioned in this article.