Get free stock price alert emails with Google Sheets, Gmail & Zapier
As an investor, I frequently come across companies that sound great on paper, but don’t have the price to match.
Let’s say I like the look of Alphabet Inc, but it’s currently sitting at $1,046.40 per share and I’m hoping to get it for under $1,000. Here’s how I set up an email alert to notify me when it does.
1. Create a new Google Sheet and get the stock price.
Using the formula =GOOGLEFINANCE(“GOOG”,”price”) I can see a time delayed price.
Next, I’ll add a target price column, and add a formula to only display the share price if it drops below the target price ($1,000).
The formula translates to: “if the price of GOOG is less than $1,000 per share, return the price of GOOG, else return nothing”. In other words “=IF(GOOGLEFINANCE(A2,”price”)<B2,GOOGLEFINANCE(A2,”price”),””)”
The reason I only want to display the price if it is below $1,000, is because I am going to set up a “trigger” in Zapier (step 2b) or a “notificaiton rule” (step 2a) that only takes action if the cell value is updated to meet that condition.
Note: Ensure that the cells have been formatted as a number, and the sheet is set to update every minute (so that the stock price will be updated even when Google Sheets is not open).
2a. Set a Google Sheets notification to email when target price is met
After posting this guide originally there was only from step 2b onwards —but I received a great comment below this article — Zapier, while it will give a slightly better solution, isn’t actually needed for a basic alert system. We can use a notification rule in Google Sheets to alert us of any changes — which will be triggered when the target price is met.
While this solution works, Zapier allows further customization to actually include what the new share price is.
2b. Create a free Zapier account, and create a new Zap
Zapier is a great tool. If you don’t have an account already, there’s a free version, which is all you need for this guide. First of all, select ‘New or Updated Spreadsheet Row’ as the “Trigger” (Triggers in this case, just mean that Zapier will start its process whenever a row is updated in Google Sheets. In this example, that ‘update’ is the formula populating the cell with a share price, as it has just dropped below the target.)
Next, select the Google Sheet you created in Step 1, the Worksheet (the default will be Sheet1, but I’ve named it GOOG), and lastly the “Trigger”, which will be ‘Price’.
3. Set up a Zapier “Action” — send Gmail
As this “Trigger” will only occur when the price drops below the target, it’s now ready to add the “Action” part of the sequence, which will be to send myself an email via Gmail.
In the subject of the email, I’m going to include the current share price of GOOG.
And that’s it! Turn your Zap on.
Now, I’m going to edit my target price to $1,100 for the purpose of a test, to trigger the email sequence.
And, there it is, a handy email from my advisor:
Want an SMS instead? No problem, use one of the SMS By Zapier option.
4. Duplicate the sequence for more alerts
You can easily create more of these sequences in Google Sheets by “duplicating” the sheet and “copying” the Zap to create more alerts, just tweak the formulas in Sheets for the ticker code, and the Sheet/Column in Zapier.
That’s it! Let me know below if you found this useful or have any questions
Found this too complicated? Have a paid Zapier account? Use a multi-step Zap instead and the Filter option to simplify the Google Sheets process, how-to guide coming soon.
Please Note: I’m not affiliated with any of the companies mentioned in this article. Also, IFTTT used to provide services that could do the above, but they appear to be down, and display a “is not a valid ticker symbol” message.
David Watts — Founder, powrbot.com
