Building a stock alert system with Google Script


This is obsolete as Yahoo stopped their quote service. I have a new spreadsheet here.

When I thought about it, I realized that my ideal system would be a spreadsheet where to add tickers and alert levels. Under the covers, the system would need to check the current price of a ticker, compare it with the alert level and send me email when triggered.

Also the whole thing shouldn’t be running from my machine at home, but from somewhere on the internet.

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

Script is here. Sheet is here.

First a utility function to send errors via email, which will be used throughout the script.

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

Then another one to check if the price downloaded from the internet is sensible.

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

We then need one to retrieve the current price of a ticker from the array of data returned from the internet:

// 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 function. First we load up the spreadsheet and get the values and headers we care about. This would be more robust 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 library to make the code a bit more maintainable, instead of scattering column numbers in the code.

Now we get all the tickers and download the prices from Yahoo (we try three times as it occasionally 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 = "" + 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)
      } 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 enter the main loop. The concept is simple, for each row we check the price and, if the price is above/below 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 variable if not null.

First we check that we haven’t already executed 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 active and if it is the right time, we check if the alert is triggered. If it is we add the text to the body variable.

      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 something was triggered, so we send the email.

    if(body != "") {
      Logger.log("Body final:%s", body)
      MailApp.sendEmail('', 'Watchlist: stops triggered', body)
      var data = ObjApp.objectToArray(headers, rows)

If an error was generated, then we send the error email.

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

My experience overall was remarkable. The learning curve was very quick and the web editor works remarkably well (well, stepping through code is rather slow).

Overall, if Google has all your data (in Drive) and you can write code to manipulate it (in Google script), why do I need my home computer again? I can just have a small screen that connects to the internet and I’m done.

That’s probably true for me apart from two things that I haven’t found in web form: editing of images in the raw format and a sophisticated portfolio application. If I find these two, I’m ready to give up my life to Google …

Letter on investing

In 2007 I wrote a blog post on investing. During the last five years, my view evolved a bit. People often ask me how to get a financial education. This is the latest email I sent on the topic.

From: Bolognese, Luca
Sent: 04 April 2012 16:52
Subject: RE: A financial education

Disclaimer: this are just my personal opinions drawn from my 10+ years of investing, reading books and academic papers about it. I can justify most statements below with some academic reference, but that would make this email too long.

Also someone else might read the same material and come up with a different opinion. It is a field that is probabilistic in nature and doesn’t lend itself to certainties. For example, you can make a lot of money out of an investment and still be wrong to have made it. And conversely.

Our brains don’t work well in such fields.

  • The most important thing in investing is not investing, it is saving the money to invest. If you save enough and are mildly reasonable in your investments (aka you diversify), you are going to be ok. Saving is not mechanically difficult, it is motivationally difficult. The best book I found on the topic is this one. I read earlier editions, this is a new one. I don’t like the investment chapters.
  • After that, you need to make sure that your financial matters are in order (i.e. you are ensured against catastrophes, you maximize your tax deductions, etc..). This is the field of personal finance. I’ve been suggesting this book to American audiences. It is on, so it might be the UK version.
  • Now that you have saved money and your finances are in order, you can start thinking about investing. The most important things in investing are: deciding what you believe in, what your risk tolerance is, how important is for your performance to track the market, what your time horizon is and how much time you want to dedicate to investing.
    • My risk tolerance is high. I’ve spent a lot of time learning about investing. I’m willing to see the value of my portfolio go down without experiencing emotional distress (I’ve tried it)
    • I don’t care about my investment performance tracking the market. I don’t watch financial programs.
    • My time horizon is long. These are money I invest for my retirement.
    • I’m willing to invest time to keep myself up to date on investment topics and manage my portfolio
    • I believe the following (these are the biases that colour my view):
    • Diversification among different sources of returns is to be sought aggressively
    • Most asset classes are not efficient because of:
      • Predictable flaws in human brain’s processing machinery
      • Institutional constraints on most professional investor (aka they have to track the market they invest in closely)
      • Short term performance incentive for professional investors
    • Some asset classes are intrinsically more inefficient than others (i.e. emerging markets micro stocks compared to US large stock) because that’s where the causes of inefficiencies are strongest
    • The biggest inefficiencies are among asset classes (i.e. stocks vs bonds vs commodities)
    • Most people (myself included) don’t have the time to do the research necessary to invest in individual stocks using the two ways that I believe are ‘right’: quant models or/and fundamental value analysis.
  • Some books to get you started:
    • A good perspective on how to think about the market and the work you need to do if you want to invest in stocks. The intelligent investor.
    • Why diversification is important. The intelligent asset allocator. I don’t believe in static asset allocation, but you need to know what it is to believe (or not) in it.
    • Moving beyond a static asset allocation with a quant based highly diversified, but simple to implement system. The Ivy Portfolio. I am very tempted to use this one myself
    • Why asset classes are often mispriced. Probably the premier quant shop around. Read most quarterly letters.
    • Systems to take advantage of such mispricing are described here. I have obtained copies of all the newsletters sent by the author going back to 1991 and backtested some of the systems. These are the system I use. But the newsletter is relatively expensive and complex to follow.
    • If you decide to branch out into value analysis of companies. Essays of warren buffett. You would also need to read an introductory and an advanced accounting text. I don’t know UK accounting, so cannot suggest good candidates.
    • If you are very conservative and want to just invest in Inflation Linked Bonds (or fearful of markets in general), there is a book for that: here.
  • With all of that out of the way, here is some practical counsel
    • if you have less than 20,000 pounds to invest try to find a fund or etf that invest in a diversified array of asset classes for a small price. I don’t know the UK market, so cannot suggest one.
    • If you have more than that and are willing to dedicate a few hours a month, you can start implementing some of the systems in the Ivy Portfolio book. Maybe you integrate it with the GMO 7 years asset class forecasts to push a bit of value bias in your system.
    • For most people that’s it. If it becomes a lifelong interest / mania as it is for me, there is a bit more that can be done, but it is unclear to me that the results are necessarily better than the simple systems described above.



Tracking spread trades in F# (and hooking up XUnit and FsCheck) – Part 1

I have a bunch of spread trades open. Spread trades are trades where you buy something and you sell something else generally in the same amount. You hope to profit from the widening of the spread between the price of the two instruments.

I place stop loss orders or trailing stops for all my trades. I have various tool that automatically notify me when a stop loss or trailing stop is hit. For spread trades I don’t have such a tool, hence I decided to build it.

I defined maximum adverse excursion for a spread trade as the percentage difference between the current value of ‘long price’ / ‘short price’ and its maximum value from the point the trade was placed (aka Current(‘long price’ / ‘short price’) / max (‘long price’ / ‘short price’) – 1 ). This goes from 0 to 100. If the maximum adverse excursion is larger than the trailing stop (using closing prices only), then I want to be notified by email.

I decided to create a simple exe and use Task Scheduler to run it at the end of the trading day. The program reads a file with all the open spread trades, downloads their prices, calculates maximum adverse excursion and sends an email if it is larger than the trailing stop. I also built a little WPF veneer to manipulate the configuration file.

Here is what my common.fs file looks like.

namespace Spread
module internal Common =
    open System

    let internal isValidDate s =
        let v, _ = DateTime.TryParse(s) 
    let internal isValidTrailingStop s =
        let v1, n = Int32.TryParse(s)
        if not(v1) then
            n >= 0 && n <= 100
    let internal isValidTicker (t:string) = not(t.Contains(","))
    let internal isValidLine (l:string) = l.Split([|','|]).Length = 4

    let internal elseThrow message expression = if not(expression) then raise message
    let internal elseThrowi i message expression = if not(expression) then failwith (sprintf "On line %i : %s" i message)


Notice the isValidTicker function. Yep, I’m using a CSV file to store the list of spread trades. Also I often end up using the little elseThrow  functions that I originally used in the Excel functions library to check preconditions.

Here is an example of using them for the parseLine function:

// parse a line in the csv config file, assumes valid csv, dates and trailing stop in [0,100]
let internal parseLine lineNumber line =
    isValidLine line                |> elseThrowi lineNumber "badly formatted line"
    let values = line.Split([|','|])
    isValidDate values.[0]          |> elseThrowi lineNumber "badly formatted date"
    isValidTicker values.[1]        |> elseThrowi lineNumber "long ticker has a comma in it"
    isValidTicker values.[2]        |> elseThrowi lineNumber "short ticker has a comma in it"
    isValidTrailingStop values.[3]  |> elseThrowi lineNumber "trailing stop has to be between 0 and 100 included"

    DateTime.Parse(values.[0]), values.[1].Trim(), values.[2].Trim(), int values.[3]


As you can see, the csv format is (dateOfTrade, longTicker, shortTicker, trailingStop). Let’s now look and the FsCheck testcase for this function.

let writeLine (date:DateTime) (tickerLong:string) (tickerShort:string) (trailingStopValue:int) =
    sprintf "%s,%s,%s,%i" (date.ToShortDateString()) tickerLong tickerShort trailingStopValue

[<Fact;Category("Fast Tests")>]
let can_parse_valid_lines () =
    let  prop_parseLine (lineNumber:int) date tickerLong tickerShort trailingStopValue =
        let line = writeLine date tickerLong tickerShort trailingStopValue
        let values = line.Split([|','|])
        (isValidLine(line) && isValidDate values.[0] && isValidTicker values.[1] && isValidTicker values.[2]
                                                                                        && isValidTrailingStop values.[3])
            ==> lazy
                let actual = parseLine lineNumber line
                (date, tickerLong.Trim(), tickerShort.Trim(), trailingStopValue) = actual
    check config prop_parseLine

In FsCheck you state properties of your functions and FsCheck generates random values to test them. In this case I’m asserting that, given a date, tickerLong, tickerShort, trailingStopValue, I can write them to a string, read them back and I get the same values. Frankly, I was skeptical on the utility of such exercise, but I was wrong. That’s how I discovered that tickers cannot have commas in them (among other things).

To hook up FsCheck and XUnit (aka to run FsCheck property checking as normal testcases), you need to write the below black magic code.

let xUnitRunner = 
    { new IRunner with 
        member x.OnArguments(_,_,_) = ()  
        member x.OnShrink(_,_) = ()
        member x.OnFinished(name, result) = 
            match result with 
                | True data -> Assert.True(true)
                | _ -> failwith (testFinishedToString name result)

let config = {quick with Runner = xUnitRunner}

Also, to run XUnit with your brand new .net 4.0 project, you need to add xunit.gui.exe.config to the XUnit directory with the following content:



<requiredRuntime version=”v4.0.20506″ safemode=”true”/>



While we are talking about such trivialities, I compile my testcases as executable, so that I can easily run them under debug. I also add the InternalsVisibleTo attribute, so that I can test internal stuff. Many of my algorithms are in internal functions and I want to test them in isolation.



Given the previous function, I can then parse text and files with the following:

let internal parseText (lines:string) = lines.Trim().Split([|'\n'|]) |> Array.mapi parseLine
let public parseFile fileName = File.ReadAllText fileName |> parseText

I need to load closing prices. I’m using my own library to load prices. That library is pretty badly designed. Also, the function below should be factorized in several sub-functions. It kind of shows how you can write spaghetti code in a beautiful functional language as F# if you really try hard. But let’s not worry about such subtleties for now …

let internal loadClosingPrices (endDate:DateTime) tickersStartDate  =
    // format parameters to conform to loadTickersAsync
    let tickersLong, tickersShort =
        |> (fun (startDate:DateTime, ticker1:string, ticker2:string, _) ->
                (ticker1, {Start = startDate; End = endDate}), (ticker2, {Start = startDate; End = endDate}))
        |> Array.unzip
    let prices = tickersShort
                 |> Array.append tickersLong
                 |> Array.toList
                 |> loadTickersAsync
                 |> Async.RunSynchronously
                 |> (fun (ticker, span, obs) -> ticker, obs (*|> asHappened 1. |> adjusted adjStart*))
    let len = tickersLong.Length
    let longObs = Array.sub prices 0 len
    let shortObs = Array.sub prices len len
    // removes divs and splits
    let choosePrices observation = match observation.Event with Price(pr) -> Some(observation) | _ -> None
    let combineOverTickerObservations f tickerObservations =
        |> (fun (ticker, observations) ->
                                            observations |> List.choose f |> List.rev)
    let longPrices = combineOverTickerObservations choosePrices longObs
    let shortPrices = combineOverTickerObservations choosePrices shortObs
    longPrices, shortPrices

In the above, tickerStartDate is an array of (trade date * long ticker * short ticker * trailingStop) which is what is produced by our parseLine function. The function first separates out long tickers from short ones.

let tickersLong, tickersShort =
    |> (fun (startDate:DateTime, ticker1:string, ticker2:string, _) ->
            (ticker1, {Start = startDate; End = endDate}), (ticker2, {Start = startDate; End = endDate}))
    |> Array.unzip

It then puts them together again in a single Array, to be able to pass it to the loadTickerAsync functions. It runs the function, waits for the results and then returns an array of (ticker * observations).

let prices = tickersShort
             |> Array.append tickersLong
             |> Array.toList
             |> loadTickersAsync
             |> Async.RunSynchronously
             |> (fun (ticker, span, obs) -> ticker, obs |> asHappened 1. |> adjusted adjStart)


The data is downloaded as it comes from Yahoo, which is a mix of adjusted and not adjusted data. asHappened transforms it so that everything is as it really happened, adjusted then adjusts it for the effect of dividends and splits. Think of this two function as ‘make the data right’.

We then split them again to get the long and short series. The point of merging them and splitting them is to call loadTickersAsync just once instead of twice. There are better ways to do it.

        let len = tickersLong.Length
        let longObs = Array.sub prices 0 len
        let shortObs = Array.sub prices len len

At this point we remove the observations that represents dividends or splits, as we are interested just in prices and we return the resulting observations.

let choosePrices observation = match observation.Event with Price(pr) -> Some(observation) | _ -> None
let combineOverTickerObservations f tickerObservations =
    |> (fun (ticker, observations) ->
                                        observations |> List.choose f |> List.rev)
let longPrices = combineOverTickerObservations choosePrices longObs
let shortPrices = combineOverTickerObservations choosePrices shortObs
longPrices, shortPrices

The List.rev at the end is interesting. Somewhere in the loadTickerAsync/asHappened/adjusted triad of functions I end up reversing the list. I should fix the bug instead of workaround it, but this is just a blog post, not production code, so I’ll let it slip.

Now that we have our price observations, we need to extract the price values and calculate the sequence of ratios (long price / short price).

let internal calcRatioSeries longPrices shortPrices =
    let extractPrice obs = match obs.Event with Price(pr) -> pr.Close | _ -> failwith "At this point divs and splits should have been removed"
    let longValues = longPrices |> extractPrice 
    let shortValues = shortPrices |> extractPrice 
    shortValues |> List.map2 (/) longValues

Having this ratio series, we can calculate the maximum adverse excursion, incorrectly called trailing stop below.

let internal calcTrailingStop ratioSeries = List.head ratioSeries / List.max ratioSeries - 1.

We then create a function that puts it all together.

type public Result = {RatioName:string; CurrentTrailing:int; TrailingStop:int} with
    override x.ToString() = x.RatioName + "\t\t" + x.CurrentTrailing.ToString() + "\t\t" + x.TrailingStop.ToString()

// reads a csv file (startDate, longTicker, shortTicker, trailingStop) and returns an array of results
let public processFile fileName endDate =
    let fileInfo = parseFile fileName
    let longPrices, shortPrices = loadClosingPrices endDate fileInfo
    let ratioSeries = Array.map2 (fun l s -> fst l + "/" + fst s, calcRatioSeries (snd l) (snd s)) longPrices shortPrices
    ratioSeries |> Array.mapi (fun i (name, series) ->
                    let (_,_,_,ts) = fileInfo.[i]
                    {RatioName = name; CurrentTrailing = - int (Math.Round (calcTrailingStop series * 100., 0));
                                                                                                       TrailingStop = ts})

The function takes a fileName and an endDate, the latter parameter is for the sake of testcases that has to work in the past, so that the data doesn’t change on them.

Now we need to send an email. The code below works for me:

let sendEmail smtpServer port fromField toField subject body (user:string) (password:string) =
    let client = new SmtpClient(smtpServer, port)
    client.Credentials <- new NetworkCredential(user, password)
    client.EnableSsl <- true
    client.Send(fromField, toField, subject, body)

// gets the password from a file under C: so that when I post it on my blog I don't forget to delete it
let getPassword () =
    File.ReadAllText(@"D:\Documents and Settings\Luca\My Documents\config.txt")

Almost done, in the main part of the program, we gather the data, create the content of the email and send it out:

    let file = "spreads.csv"
    let spreads = processFile file DateTime.Today
    let mutable builder = new System.Text.StringBuilder()
    builder <- builder.AppendLine("Name\t\tCurrent\t\tStop")
    for s in spreads do
        builder <- builder.AppendLine(s.ToString())
    let password = getPassword()
    sendEmail "" 587 "***@***.com" "***@***.com" "Alert Trigger Spread" (builder.ToString())
                                                                                           "" password;;

Next stop, the WPF veneer on top of the file.