Building a stock alert system with Google Script

This is ob­so­lete as Yahoo stopped their quote ser­vice. I have a new spread­sheet here.

When I thought about it, I re­al­ized that my ideal sys­tem would be a spread­sheet where to add tick­ers and alert lev­els. Under the cov­ers, the sys­tem would need to check the cur­rent price of a ticker, com­pare it with the alert level and send me email when trig­gered.

Also the whole thing should­n’t be run­ning from my ma­chine at home, but from some­where on the in­ter­net.

Google script fit the bill. Let’s see how it works.

Script is here. Sheet is here.

First a util­ity func­tion to send er­rors via email, which will be used through­out the script.

function emailError(e) {
  MailApp.sendEmail("lucabolg@gmail.com", "Watchlist Error",
                    "\r\nMessage: " + e.message
                    + "\r\nFile: " + e.fileName
                    + "\r\nLine: " + e.lineNumber
                    + "\r\nLOg: " + Logger.getLog())
}

Then an­other one to check if the price down­loaded from the in­ter­net is sen­si­ble.

function validPrice(price) {
 return price != 'undefined' && price > 0.1
}

We then need one to re­trieve the cur­rent price of a ticker from the ar­ray of data re­turned from the in­ter­net:

// Find the current price of a ticker in an array of data where the ticker is the first column
function getQuote(data, ticker) {
  var ticker = ticker.trim().toLowerCase()

  for(var i = 0; i = 22 && hour <= 23) || value != "close"
}

With all of that in place, we can now look at the main func­tion. First we load up the spread­sheet and get the val­ues and head­ers we care about. This would be more ro­bust if we looked up the sheet by name. Also the id of the sheet is burned in the code. You’ll need to change it if you want to make it point to your own.

// Check spreadsheet with tickers and stop prices, send email when a stop is hit and mark the row as 'Executed'.
function checkQuotes() {
 try {

 // Get all data from spreadsheet in one web call.
 var ss = SpreadsheetApp.openById("1WQf2AiBPQW5HLzCyGgsFlKN0f1HTOWAteJ5bJCXVnlc")
 var range = ss.getSheets()[0].getDataRange()
 var values = range.getValues()
 var headers = values[0]
 var rows = ObjApp.rangeToObjects(values)
 var body = ""
 var now = new Date()

Notice ‘ObjApp’ is part of the ObjService li­brary to make the code a bit more main­tain­able, in­stead of scat­ter­ing col­umn num­bers in the code.

Now we get all the tick­ers and down­load the prices from Yahoo (we try three times as it oc­ca­sion­ally fails.

    // Fish out all tickers from col 0 where Status (col 4) is not executed
    var tickers = []
    for(var i = 1; i < rows.length; i++) {// dont' process the headers
      if((rows[i]).executed.toLowerCase() == 'active' && isRightTime(rows[i], now)) tickers.push((rows[i]).ticker.trim().toLowerCase())
    }
    Logger.log("Tickers:%s" ,tickers)

    if(tickers.length == 0) return // Nothing to process

    // Get ticker, real time bid, real time ask for all tickers in one web call
    var url = "http://finance.yahoo.com/d/quotes.csv?s=" + tickers.join("+") + "&f=sl1"//"&f=sb2b3"

    // Try 3 times before giving up
    for(var i = 0; i < 3; i++) {
      try {
        var response = UrlFetchApp.fetch(url)
        break;
      } catch(e) {
      }
    }

    Logger.log("Response:\n%s", response)
    var data = Utilities.parseCsv(response.getContentText())
    Logger.log("Data:\n%s", data)

Once that is done, we en­ter the main loop. The con­cept is sim­ple, for each row we check the price and, if the price is above/​be­low the alert we add it to the body string and mark the row in the sheet so that we don’t process it again next time. A the end, we email the body vari­able if not null.

First we check that we haven’t al­ready ex­e­cuted this row:

    for(var i = 1; i < rows.length; i++) {// dont' process the headers
      var current = rows[i]
      if(current.executed.trim().toLowerCase() == 'executed') continue // no need to process it as it is 'Executed'

      var symbol = current.operator
      var stop = current.stop

If it’s still ac­tive and if it is the right time, we check if the alert is trig­gered. If it is we add the text to the body vari­able.

      if(isRightTime(current, now)) {
        var price = getQuote(data, current.ticker)
        if( (symbol.trim() == ">" && price > stop) ||
           (symbol.trim() == "<" && price < stop)) {

          current.executed = "Executed"
          current.price = price

          body += [current.kind, current.ticker, current.price, current.operator, current.stop, "\r\n"].join(" ")
          Logger.log("Body in loop:\n%s", body)
        }
      }
    }

If body is not empty, that means that some­thing was trig­gered, so we send the email.

    if(body != "") {
      Logger.log("Body final:%s", body)
      MailApp.sendEmail('lucabolg@gmail.com', 'Watchlist: stops triggered', body)
      var data = ObjApp.objectToArray(headers, rows)
      data.unshift(headers)
      range.setValues(data)
    }

If an er­ror was gen­er­ated, then we send the er­ror email.

  } catch (e) {
    Logger.log(e.lineNumber + ":" + e.message)
    emailError(e)
  }
}

My ex­pe­ri­ence over­all was re­mark­able. The learn­ing curve was very quick and the web ed­i­tor works re­mark­ably well (well, step­ping through code is rather slow).

Overall, if Google has all your data (in Drive) and you can write code to ma­nip­u­late it (in Google script), why do I need my home com­puter again? I can just have a small screen that con­nects to the in­ter­net and I’m done.

That’s prob­a­bly true for me apart from two things that I haven’t found in web form: edit­ing of im­ages in the raw for­mat and a so­phis­ti­cated port­fo­lio ap­pli­ca­tion. If I find these two, I’m ready to give up my life to Google …

Tags

• FINANCIAL

5 Comments

Comments

Tonio

2017-10-17T09:19:43Z

Hi,
Thanks for shar­ing.
Do I need to have the sheet open and man­u­ally run the macro to up­date price data and trig­ger alert or does it work in the cloud, with my com­puter shut­down and me away? I could then re­ceive the emails on my smart­phone and take proper ac­tion, which would be fan­tas­tic!

lucabol

2017-10-17T11:38:55Z

Hi Tonio, it works in the cloud. Just change the email to send emails to.

JC

2018-06-07T02:52:20Z

Hi, as a non-tech­ni­cal per­son, I was won­der­ing, does the script run by it­self or do you need to man­u­ally click “run” each time in Script Editor?
The price trig­gers for some stocks were reached on Day 1, and the email was sent. But on Day 2, even though it was still within the trig­gered price, the email was­n’t sent, and when I went into the Script Editor to click “Run” , the email was sent. Am I do­ing some­thing wrong in the set up? Do I need to go into “Publish” menu and “Deploy as..” some­thing?

lucabol

2018-06-07T05:41:40Z

Hi, I thought this would be copied when you copy the spread­sheet, but maybe not. Go to File/Spreadsheet Settings/Calculation and set Recalculation to ‘On change and every min­ute’. This is how I have it set up.

JC

2018-06-09T04:34:19Z

You are right that the Spreadsheet Settings was copied over, so that’s not the is­sue.
I’n happy to say I found a workaround af­ter I Googled around! I added a Project Trigger in the Code to run your check­Stop func­tion every 5 min­utes, and that seems to work! Super stoked. Thank you for the help and pro­vid­ing this tem­plate!