Stock Buy Helper is designed to track data on securities. When the volume of trading for any ticker exceeds the threshold value, a color alarm is generated and a notification is sent by email. It can be especially interesting for investors in high-yield risky securities, for example, in warrants.
Data is loaded daily at a time set by a time trigger or at any time manually. When Stock Buy Helper sees that the trading volume exceeds the threshold value, it will immediately send you an email notification.
If the download is performed after the end of trading, then the following are loaded:
- Trading volume today,
- Trading volume yesterday,
- Closing price today,
- Closing price yesterday.
If the download is made during the trading process, then the following are loaded:
- The volume of trades on the date and time of loading,
- The volume of trades yesterday,
- The price on the date and time of loading,
- The price of closing yesterday.
Ticker data can be downloaded from any sites that display content without using JS, for example, from Yahoo Finance.
Calculated:
- Change in volume, pcs.,
- Change in volume,%,
- Change in price, $,
- Change in price,%,
- Comparison of the last trading volume with the average volume for 3 previous days,%.
Procedure
1. Create a copy of the Stock Buy Helper spreadsheet.
2. In cell B2 of the «Settings» sheet, enter an email* to receive notifications. In cell C2 of the «Settings» sheet, enter the trigger time (use integers from 0 to 23).
3. From the Monitor menu, select Set Triggers.
4. Give all required permissions. This is done once on first launch (Fig. 1 — 5 for help).
5. Enter securities tickers in column A of the «Tickers» sheet and thresholds in column B. **
As well you can load an array of tickers with copy-paste.
When entering one by one, the ticker text is automatically converted into an active link.
Follow the link to make sure the ticker was entered correctly.
6. Select «Create Sheets» from the Monitor menu.
New sheets will be automatically created with ticker names, required formulas and formatting.
Data will be loaded onto these sheets in the future.
7. Since there is no data on the ticker sheets yet, on the «Tickers» summary sheet there are zeros, blank or the text «No data» in the corresponding rows. If this annoys you, you can download the data manually 🙂 To do this, select the «Download manually» item in the «Monitor» menu. ***
8. But it is better to leave the Monitor alone for a couple of days 🙂
It will load the data on your shares portfolio at the time specified by you on the «Settings» sheet.
Please note that loading is NOT done on hidden sheets and on the «ReadMe», «Settings» and «Tickers» sheets. This can be used to avoid loading data on certain sheets — just hide them.
9. If an increase in the trading volume for some security exceeds the threshold value, this value on the «Tickers» sheet will turn green and a notification will be sent by email (see Fig. 6).
10. Checking if the preset threshold is exceeded is performed immediately after the data is loaded (regardless of whether the download was performed automatically by a time trigger or manually via the menu).
The difference is calculated between two records — the last one and the penultimate one.
If a day has passed between the data downloads (one time trigger is set), then the delta of the trading volume is calculated between today’s and yesterday’s closing volumes.
If less than a day has passed between data downloads (more than one time trigger is installed or the download is performed manually before the end of trading), then the delta of the trading volume is calculated between the trading volumes for the last and the penultimate download.
(*) You can send notifications to Telegram using bots, for example, @etlgr_bot and @wwpagerbot
(**) Don’t leave blank lines!
(***) You can manually load the data or add additional time triggers to get data before the close of trading. The order is described here: Manually managing triggers. Function name: runDownload, source: by time, type: by day.
Study Google Spreadsheets in 5 Hours in an Online Course!