Strategies on The Cloud: TAA on Google Docs

Did you want to have a strategy on the cloud that monitors the market and updates you on new Buy/Sell signals (as well as number of shares, etc)  by email. Did you want to run it on best of breed “always ON” servers with free and accurate data?
How much would that set you back?Well, Nada! Courtesy of Google.This post will guide you through coding a simple Tactical Asset Allocation on Google’s Docs.
You need:
1. A Google account.
2. Google Docs.The system is similar to Faber’s TAA model using 5 Etfs.: SPY,TLT,VNQ,EEM,DBC
We buy or sell at the beginning of the month ONLY.
If Close > 200-moving Average then we buy the ETF.
If Close < 200-moving Average then we sell the ETF.

Pseudo Code:
If TodayIsNewMonth AND CloseETF>MA(200) Then Buy
If TodayIsNewMonth AND CloseETF<MA(200) Then Sell

Let’s get started. Go to Google Docs and create a new SpreadSheet. Call it TAA_5.
Once the spreadsheet is open in your browser, go up to the menu and select Tools–>Script Editor…
This should open a new script Editor. Select “SpreadSheet” as your project.

TTA5g-300x152

Lets start coding.
Google Docs scripting uses a version of JavaScript which seems fairly easy for non programmers.

It would be nice to create an object that holds all the ETF information.
So here’s the function to store each ETF’s info:

function tickerobj(symbol,close,action,posscore,sma)
 {
//tickerobj("SPY",154,1,20,150)
  this.symbol=symbol;
   this.close=close;
     this.action=action;
     this.posscore=posscore;
       this.sma=sma;
}
How do we retrieve data from Google?

function myGetHistoricalStockInfo(symbol,days)
{

 var start=new Date();
 var finish=new Date();
  start.setDate(finish.getDate()-days);
  finish.setDate(finish.getDate());

  var data = FinanceApp.getHistoricalStockInfo(symbol, start, finish, 1 );

  if(data==undefined)
    return(0);
  else
    return data;
}
We need to calculate the Simple Moving Average from the Data to check if close>sma(200).

function SMA(data,period)
{
 //data is a FinanceApp.getHistoricalStockInfo object array
  var end=data.stockInfo.length-1;

  var close=[];
  var sum=0;var count=0;
  for (var i=end;i>end-period;i--)
  {
  sum += data.stockInfo[i].close;
   count++;
  }
  return (sum/period);
}
How do we know it’s the beginning of the month?

function NewMonth()
{
var now= new Date();
var  yesterday= new Date();
 yesterday.setDate(now.getDate()-1);

  if(now.getMonth()!=yesterday.getMonth())
    return (1);
  else
    return (0);
}
So let’s start calculating and storing info for each ETF. Tickerlist would be “SPY,EEM,…etc”.
function CreateInstr(tickerlist,SMAperiod)
{
  var symbolarray=[];
//split tickerlist by comma (",")
   symbolarray= tickerlist.split(",");
//how many symbols in the list
  var idx=symbolarray.length;
  
  var close=[];var smat=[];var posscore=[];var action=[];//var symbol=[];
  var Instrument=[]; var idx1; var data=[];
//Go through each symbol. I.e. SPY...then EEM...
   for (var i=0;i<idx;i++) 
   { 
//get hist data
      data=myGetHistoricalStockInfo(symbolarray[i],SMAperiod*2);
//get index of the last data point
      idx1=data.stockInfo.length-1;
//get the last closing price
     close[i]=data.stockInfo[idx1].close;
//get the moving average
     smat[i] = SMA(data, SMAperiod);
//not used in this example
     posscore[i]=0;
//action - If close>mov. average, 1(buy) otherwise -1(sell)
     action[i]=(close[i]>smat[i])?1:-1 ; 
     
//Now store all the infor in the object
      Instrument[i]=new tickerobj(symbolarray[i],close[i],action[i],posscore[i],smat[i]);
   }
return(Instrument);
}

So let’s show all this info on the Spreadsheet.

function ShowTAAOnSpreadsheet()
{
  var sheet = SpreadsheetApp.getActiveSheet();
  var date=new Date();
  //You may use your own ticker list and period
   var Instrument=CreateInstr("TLT,SPY,EEM,VNQ,DBC",200);

//Set the Name headers for each column
  sheet.getRange(3,1).setValue("Symbol");
    sheet.getRange(3,2).setValue("Close");
    sheet.getRange(3,3).setValue("SMA");
    sheet.getRange(3,4).setValue("Action");

  var idx=Instrument.length;

     for (var i=0;i<idx;i++)
   {
       sheet.getRange(5+i,1).setValue(Instrument[i].symbol);

       sheet.getRange(5+i,2).setValue(Instrument[i].close);
   
       sheet.getRange(5+i,3).setValue(Instrument[i].sma);
   
       sheet.getRange(5+i,4).setValue(Instrument[i].action);
   }
  sheet.getRange(1,1).setValue("Last Update");
  sheet.getRange(1,2).setValue(date);

}

Now you can go on the top Menu and select a function to run. Select “ShowTAAOnSpreadsheet”.
Press the “paly” button to run.

Go to the spreadsheet, see if it updated.

Last is a function to email ourselves the results. We should schedule this to run everyday (Menu–>Resources–>Current project’s Triggers).
Please do not abuse Google’s Servers by having it run too often. They are kind enough to provide this kind of functionality free of charge.

function EmailPositions()
{
 //if not new month do not calculate or email anything, just exit with 0. 
  if (NewMonth()==0)
    return (0);
  
    var email = Session.getActiveUser().getEmail();  
   var Instrument=CreateInstr("TLT,SPY,EEM,VNQ,DBC",200);
   var idx=Instrument.length;
  var txt="";
  
  
     for (var i=0;i<idx;i++) 
   { 
     txt=txt+Instrument[i].symbol+","+"  Action:  "+Instrument[i].action+"n";
   }
  
   MailApp.sendEmail(email, "TAA_5_FromGoogleDocs", txt);

}

You can easily customize the code to your needs. For example it should be fairly easy to calculate risk parity allocations weights and have them emailed to you.

Again, please do not abuse Google’s servers. Let’s keep this functionality free.

Here’s the full code.

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
function ShowTAAOnSpreadsheet()
{
  var sheet = SpreadsheetApp.getActiveSheet();
  var date=new Date();
  
    var Instrument=CreateInstr("TLT,SPY,EEM,VNQ,DBC",200);
  
//Set the Name headers for each column
  sheet.getRange(3,1).setValue("Symbol");
    sheet.getRange(3,2).setValue("Close");
    sheet.getRange(3,3).setValue("SMA");
    sheet.getRange(3,4).setValue("Action");
  
  var idx=Instrument.length;
  
     for (var i=0;i<idx;i++) 
   { 
       sheet.getRange(5+i,1).setValue(Instrument[i].symbol);

       sheet.getRange(5+i,2).setValue(Instrument[i].close);
     
       sheet.getRange(5+i,3).setValue(Instrument[i].sma);
     
       sheet.getRange(5+i,4).setValue(Instrument[i].action);
   }
  sheet.getRange(1,1).setValue("Last Update");
  sheet.getRange(1,2).setValue(date);
  
}




function CreateInstr(tickerlist,SMAperiod)
{
  
  var symbolarray=[];
   symbolarray= tickerlist.split(",");
  var idx=symbolarray.length;
  
  var close=[];var smat=[];var posscore=[];var action=[];//var symbol=[];
  var Instrument=[]; var idx1; var data=[];

   for (var i=0;i<idx;i++) 
   { 
      data=myGetHistoricalStockInfo(symbolarray[i],SMAperiod*2);
      idx1=data.stockInfo.length-1;
     //symbol[i]=data.stockInfo.symbol;
     close[i]=data.stockInfo[idx1].close;
     smat[i] = SMA(data, SMAperiod);
     posscore[i]=0;
     action[i]=(close[i]>smat[i])?1:-1 ; 
     
      Instrument[i]=new tickerobj(symbolarray[i],close[i],action[i],posscore[i],smat[i]);
   }
return(Instrument);
}




function myGetHistoricalStockInfo(symbol,days) 
{

 var start=new Date();
 var finish=new Date();
  start.setDate(finish.getDate()-days);
  finish.setDate(finish.getDate());
  
  var data = FinanceApp.getHistoricalStockInfo(symbol, start, finish, 1 );

  if(data==undefined)
    return(0);
  else 
    return data;
}


function SMA(data,period)
{
 //data is a FinanceApp.getHistoricalStockInfo object array
  var end=data.stockInfo.length-1;
  
  var close=[];
  var sum=0;var count=0;
  for (var i=end;i>end-period;i--)
  {
  sum += data.stockInfo[i].close;
   count++;
  }
  return (sum/period);
}



function NewMonth()
{
var now= new Date();
var  yesterday= new Date();
 yesterday.setDate(now.getDate()-1);
  
  if(now.getMonth()!=yesterday.getMonth())
    return (1);
  else
    return (0);
}



function tickerobj(symbol,close,action,posscore,sma) {
  this.symbol=symbol;
   this.close=close;
     this.action=action;
     this.posscore=posscore;
       this.sma=sma;
}

function EmailPositions()
{
 //if not new month do not calculate or email anything, just exit with 0. 
  if (NewMonth()==0)
    return (0);
  
    var email = Session.getActiveUser().getEmail();  
   var Instrument=CreateInstr("TLT,SPY,EEM,VNQ,DBC",200);
   var idx=Instrument.length;
  var txt="";
  
  
     for (var i=0;i<idx;i++) 
   { 
     txt=txt+Instrument[i].symbol+","+"  Action:  "+Instrument[i].action+"n";
   }
  
   MailApp.sendEmail(email, "TAA_5_FromGoogleDocs", txt);

}

7 thoughts on “Strategies on The Cloud: TAA on Google Docs”

  1. Great work, Sanz Prophet. I still use Google Apps Script to automate much of my trading through Collective2, although your code is far more elegant (I'm still struggling to make the transition to using objects instead of several variables for each instrument). Thanks for the examples.

  2. Hi INTJ. Yes, I also used to trade YoutualFunds with scripts like this one.
    Posscore stands for "position score". We can assign to this value a metric and then Sort by this metric.
    Let's say we want to use a momentum filter and trade only the 3 ETFs that performed best the last 3 months. We can create a function ROC(90) and assign posscore=ROC(90) for each ETF. We can then Sort by posscore and buy the top three only.

  3. Thanks for the explanation. Speaking of the cloud, are you aware of any web services that allow trading through Interactive Brokers? Quantopian seems to be taking its time, although in all fairness I have heard that IB is a particularly difficult partner to work with.

  4. I know of Quantopian https://www.quantopian.com/
    and Rizm http://equametrics.com/
    both of which are not in 'live' mode yet. Rizm beta-testers are paper-trading.

    In theory one can build his/her own cloud service by running an EC2 instance of TWS on the cloud. The question is what software is robust enough to run together with TWS unattended on the cloud. It would be a great business if someone could sell EC2 pre-configured AMI's with (strategy friendly) trading software and robust fault-tolerant IB interaction.

  5. Regarding EC2 AMI's with trading software for IB interaction, my product Brokertron Gateway for IB
    http://www.brokertron.com/gateway/
    is server software for enabling server/cloud oriented trading software to connect to the IB API. Specifically, Brokertron Gateway for IB makes possible IB login with an IB security device via a web browser instead of via remote desktop (or VNC) to an IB Gateway (or TWS) GUI application.

    Instead of selling a specific AMI, I deliver an RPM package and a simple fully automatic installation script that gives you a fully functional pre-configured server using appropriate public AMIs. See
    http://www.brokertron.com/gateway/admin.html
    for the full details.

Leave a Reply

Your email address will not be published. Required fields are marked *