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.
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; }
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 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.
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); }
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); } |
INTJ Capital says
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.
INTJ Capital says
Also, what is "posscore" used for?
Sanz Prophet says
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.
INTJ Capital says
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.
Sanz Prophet says
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.
E. Castedo Ellerman says
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.
Anonymous says
Any alternative to FinanceApp after Google depreciated it?
Thx