Building a basic technical trading tool

#1

This is a very basic template for a tool that - if built out - could ultimately be pretty powerful using dashdash. It’s a technical trading tool that uses changes in patterns of stock prices’ simple moving averages (SMAs) as indicators to buy or sell stocks - although you could in theory use it for any kind/combination of financial instrument(s).

I’ve structured the tool according to two simple rules: if the 50-day SMA of a stock price moves above the 200-day SMA, this is a buy signal; if the 50-day SMA moves below the 200-day SMA, this is a sell signal. When either of these two events happen for a stock in the tool, I will be sent an email instructing me to trade accordingly. You could build an instruction to trade on an actual account if you had sufficient conviction in the rules underlying your model… I don’t - my rules are just intended to illustrate the concept.

At the moment, the tool contains only two stocks. Adding more would just involve copying and pasting the formulae I’ve used. However, to make the process of copying and pasting easier, I would actually structure the sheet differently to how I have done and how I outline below - I’d build each table to the right of the last (i.e. horizontally, whereas the current format is structured predominantly vertically). I’ve structured my tool the way I have so that it all fits on one sheet and I don’t need to scroll. It looks like this:

The data informing the trade decisions come from the Alpha Vantage SMA API. It’s essentially the same as the daily time series API used by Humberto in this post, as is the way I’ve constructed this part of the tool. However, as there are a few differences, I’ll go through what I’ve made line by line so you can reconstruct it/build on it easily if you want to.

We begin with the API key:

  • A1: Alpha Vantage API key
  • B1: F4VSS…

B1 is the API key, which is super easy to get from the Alpha Vantage website.

A number of the functions in this sheet will need to be repeated daily in order for the tool to work without us having to keep refreshing it manually. As such, we include a repeat function at the top that will be used to refresh all the necessary cells in the sheet, without including the repeat function in each cell as this is expensive:

  • C1: Repeat function
  • D1: =REPEAT(1,1440)

1440 is the number of minutes in a day, meaning that this will repeat once every 24 hours.

Moving down, we identify the tickers of the stocks we want to include in our model. I’ve gone with Facebook and Google:

  • A3: Stock
  • A4: FB
  • A5: GOOG

Next, in the second column, we make a request to the Alpha Vantage SMA API for the 200-day SMAs of these stocks, which comes back in JSON format:

The IF structure in B4 and B5 triggers the REPEAT function in D1, following the structure suggested at the bottom of Humberto’s fin-tracker post. By the way, you’ll see in the queries in B4 and B5 that the interval=daily, the time_period=200, and the series_type=close. This means I’m requesting the 200-day SMA of the daily closing price - and the JSON will contain this data for a LOT of trading days up to today’s date.

So, next, we have to specify the two dates for which we want the SMAs - yesterday and today:

  • C3: T-1
  • C4: =IF($D$1,TODAY()-1,“NotRunning”)
  • C5: =IF($D$1,TODAY()-1,“NotRunning”)
  • D3: T
  • D4: =IF($D$1,TODAY(),“NotRunning”)
  • D5: =IF($D$1,TODAY(),“NotRunning”)

We’re using the IF function again with reference to cell D1 because these cells need to be refreshed daily. (By the way, the data is based on trading days only (i.e. Mon-Fri usually), so if you try and build this at the weekend you will get #N/A error messages as there will be no SMA for today’s date.)

The next step is to parse the JSON displayed in B4:B5 to get the SMAs as at today and yesterday, referencing the cells containing dates we’ve just created. dashdash doesn’t currently have a way of accessing the date display format when using a cell in other formulae but there’s a way round this, as explained here and used below:

  • E3: 200-day SMA T-1
  • E4: =PARSE($B4,"[‘Technical Analysis: SMA’].[’"&year(C4)&"-"&if(month(C4)<10,“0”&month(C4),month(C4))&"-"&if(day(C4)<10,“0”&day(C4),day(C4))&"’].[‘SMA’]")
  • E5: =PARSE($B5,"[‘Technical Analysis: SMA’].[’"&year(C5)&"-"&if(month(C5)<10,“0”&month(C5),month(C5))&"-"&if(day(C5)<10,“0”&day(C5),day(C5))&"’].[‘SMA’]")
  • F3: 200-day SMA T
  • F4: =PARSE($B4,"[‘Technical Analysis: SMA’].[’"&year(D4)&"-"&if(month(D4)<10,“0”&month(D4),month(D4))&"-"&if(day(D4)<10,“0”&day(D4),day(D4))&"’].[‘SMA’]")
  • F5: =PARSE($B5,"[‘Technical Analysis: SMA’].[’"&year(D5)&"-"&if(month(D5)<10,“0”&month(D5),month(D5))&"-"&if(day(D5)<10,“0”&day(D5),day(D5))&"’].[‘SMA’]")

We then replicate what we’ve done in cells A3:F5 for the 50-day SMA. In my tool I did this below the previous section in cells A7:F9 so that the whole tool is easily visible without having to scroll. However, as mentioned above, if you actually want to build the tool out it’s better to put these to the right of A3:F5 because then you can copy and paste the formulae downwards as you add more stocks.

I won’t walk through this part as the logic and structure are identical to what we did for the 200-day SMA. Just note that the query needs to be amended to request the 50-day SMA, and so should read: "https://www.alphavantage.co/query?function=SMA&symbol="&A4&"&interval=daily&time_period=50&series_type=close&apikey="&$B$1&"

Once we’ve created a table for the 50-day SMA, we compute whether there’s been a shift in the SMAs that constitutes a buy or sell indicator. I did this from A11:E13, so will follow that structure here. However, again, if you want to be able to build the tool out easily with lots of stocks, I suggest structuring this to the right of the previous tables.

First, to determine whether the 50-day has moved above or below the 200-day SMA from yesterday to today, we calculate the difference between the SMAs yesterday and the difference today. To do this, we subtract the 200-day SMA from the 50-day both yesterday and today. If the result was negative yesterday and is positive today, we know that the 50-day has moved above the 200-day and this is a buy indicator - and vice versa for the sell indicator.

We calculate the differences for yesterday and today in the following cells:

  • A11: Stock
  • A12: FB
  • A13: GOOG
  • B11: SMA difference T-1
  • B12: =E8-E4
  • B13: =E9-E5
  • C11: SMA difference T
  • C12: =F8-F4
  • C13: =F9-F5

Now, we need to interpret the move from yesterday to today and whether this constitutes a buy or a cell indicator. Using the following functions, we can interpret the data and in the same cell insert a buy or sell instruction:

  • D11: BUY
  • D12: =IF(AND($B12<0,$C12>0),CONCATENATE(D$11,$A12),’-’)
  • D13: =IF(AND($B13<0,$C13>0),CONCATENATE(D$11,$A13),’-’)
  • E11: SELL
  • E12: =IF(AND($B12>0,$C12<0),CONCATENATE(E$11,$A12),’-’)
  • E13: =IF(AND($B13>0,$C13<0),CONCATENATE(E$11,$A13),’-’)

If a buy indicator is triggered, the instruction in D12 and D13 will be be 'D11 +stock name - and likewise for the sell indicator but in column E instead of column D. It’s not very elegant but so that the buy/sell indicators are separated by a space (e.g. so it reads ‘BUY FB’ rather than ‘BUYFB’), we have to make sure to type D11 and E11 as 'BUY ’ and 'SELL '.

Now we’ve created the part of the tool that obtains the data and determines whether it’s a buy, sell, or nothing (’-’), we need to create the part that sends an email with the appropriate trade instructions. For this I used the SendGrid API, as explained by Torben here. For those that haven’t read Torben’s post, I’ll quickly demonstrate how I used the API.

First, we need to detail the request URL, API key and the email content:

Then, we create the POST request to send the email based on our buy indicators and sell indicators. We only want an email to send when D12:E13 are populated with something other than ‘-’ - i.e. when a buy or sell indicator appears. So, we insert the following:

  • H12: Stock
  • H13: FB
  • H14: GOOG
  • I12: Buy post request
  • I13: =IF($D12<>’-’,POST($I$1,’{“Authorization”:“Bearer ‘&$I$2&’”}’,’{ “personalizations”: [{“to”: [{“email”: “’&$I$3&’”,“name”: “’&$I$4&’”}],“subject”: “’&$I$5&’”}],“from”: {“email”: “’&$I$6&’”,“name”: “’&$I$7&’”},“reply_to”: {“email”: “’&$I$8&’”,“name”: “’&$I$9&’”},“subject”: “’&$I$10&’”,“content”: [{“type”: “text/html”,“value”: “<html><p>’&$D12&’</p></html>”}]}’))
  • I14: =IF($D13<>’-’,POST($I$1,’{“Authorization”:“Bearer ‘&$I$2&’”}’,’{ “personalizations”: [{“to”: [{“email”: “’&$I$3&’”,“name”: “’&$I$4&’”}],“subject”: “’&$I$5&’”}],“from”: {“email”: “’&$I$6&’”,“name”: “’&$I$7&’”},“reply_to”: {“email”: “’&$I$8&’”,“name”: “’&$I$9&’”},“subject”: “’&$I$10&’”,“content”: [{“type”: “text/html”,“value”: “<html><p>’&$D13&’</p></html>”}]}’))
  • J11: Sell post request
  • J12: =IF($E12<>’-’,POST($I$1,’{“Authorization”:“Bearer ‘&$I$2&’”}’,’{ “personalizations”: [{“to”: [{“email”: “’&$I$3&’”,“name”: “’&$I$4&’”}],“subject”: “’&$I$5&’”}],“from”: {“email”: “’&$I$6&’”,“name”: “’&$I$7&’”},“reply_to”: {“email”: “’&$I$8&’”,“name”: “’&$I$9&’”},“subject”: “’&$I$10&’”,“content”: [{“type”: “text/html”,“value”: “’&$E12&’”}]}’))
  • J13: =IF($E13<>’-’,POST($I$1,’{“Authorization”:“Bearer ‘&$I$2&’”}’,’{ “personalizations”: [{“to”: [{“email”: “’&$I$3&’”,“name”: “’&$I$4&’”}],“subject”: “’&$I$5&’”}],“from”: {“email”: “’&$I$6&’”,“name”: “’&$I$7&’”},“reply_to”: {“email”: “’&$I$8&’”,“name”: “’&$I$9&’”},“subject”: “’&$I$10&’”,“content”: [{“type”: “text/html”,“value”: “’&$E13&’”}]}’))

And that’s it. Cells I12:J13 will show FALSE unless D12:E13 are populated with a buy or sell instruction. You can test whether your tool is working by playing around with the numbers in cells B12:C13 so that buy/sell indicators are triggered in D12:E13 and trade instructions are sent to you via email.

I hope it’s clear. This is my first time building something on dashdash so I’d love to receive suggestions on ways I could improve it!

Luca

:v:

4 Likes