The following excel file is an example of how to calculate the MACD indicator. This example shows the 5, 13, 6, setting. the MACD is in column N. Over time I may add more excel indicator examples. As I do they will be most easily found in the right sidebar under the “How to” – “Indicators” category.
Excel question
April 15, 2009 By Leave a Comment
This post answers the following question.
“I have recently been trying to chart historical data in Excel in a bar chart format and found the “canned” graph allows only for high, low, and close, but not the open. Do you have a simple way to add the open tick to a bar chart in Excel?”
There are four types of stock charts included in excel.
High Low Close
Open High Low Close
Volume High Low Close
Volume Open High Low Close
Select the Open High Low Close option.
If you want to also plot volume which I suspect may be the case, I always just used the Open High Low close chart then add in the volume as a new bar chart on the stock chart. To do this:
first setup your stock chart with open high low close. It should look something like this.
Next
click on the outer edge of the chart so that it is highlighted, click on “chart” from the menu bar and select “add data”. Select the volume column as your data.
Now your chart will look messed up because volume is plotted on the primary axis (along with price).
To change this just right click on the volume bar series (on the chart itself) and select “format data series”. next click on the “axis tab” and change to “secondary axis”. Next you may want to change the secondary axis scale so that volume stays way down below price. Once that is done your chart should look something like this.
How to post your ATS trades in twitter using Excel
April 14, 2009 By Leave a Comment
As an example, the following Excel script can be used to post your automated trading system trades into twitter via API. This code can easily be changed to do whatever you want. But in this example it will just be for sending a trade.
Sub TWITTER()
Dim symbol As String
Dim Price As String
Dim xml, Username, Password, tweet
Set xml = CreateObject(“MSXML2.XMLHTTP”)
‘change this to your username in quotes
Username = “yourusername”
‘change this to your password in quotes
Password = “yourpassword”
‘let the symbol variable have the symbol name from your excel sheet
symbol = Workbooks(“yourworkbook.xls”).Worksheets(“yoursheetname”).Range(“a1 or your range”)
‘let the Price variable have the symbol name from your excel sheet
Price = Workbooks(“yourworkbook.xls”).Worksheets(“yoursheetname”).Range(“a2 or your range”)
‘contruct the “tweet” this would create “Just bought a gazillion shares of (whatever $SYMBOL you have in A1) @ (whatever price you have in A2)
tweet = “Just bought a gazillion shares of ” & “$” & symbol & ” @ ” & Price
‘post the tweet
xml.Open “POST”, “http://” & Username & “:” & Password & “@twitter.com/statuses/update.xml?status=” & tweet, False
xml.setRequestHeader “Content-Type”, “content=text/html; charset=iso-8859-1″
xml.Send
Set xml = Nothing
End Sub
If added to an existing excel trading system it may be best to set this to run 20 seconds or so after your actual trade is sent to avoid any interference which can be done as follows
Application.Ontime Now() + timeserial(0, 0, 20), “TWITTER”
How to add an indicator to Simulator.xls Chart (or any Excel Stock Chart).
April 10, 2009 By 1 Comment
If you have ever tried to add a simple moving average (or any data series) to a stock chart you may have noticed that after adding the series, the chart got all messed up!
Here is a short tutorial and an example file showing how to add a simple moving average to a stock chart. I am going to use the Simulator.xls as and example. It should be noted that all the unused columns on the BarData sheet (currently K-IV) of the simulator can be used to add indicators of your choice.
Trick number one. the =NA() trick
To plot a simple moving average (SMA), the formula is =AVERAGE(E132:E150)
So that would go in R150 on BarData Sheet.
There is a problem though. Excel will try to plot something when there is not enough data to average or even when there is an error. Here is an example of a chart we are trying to avoid.
The keep the chart clean, I want to generate the error NA# whenever I do not want anything plotted.
so to keep things looking right, change the formula in R150 to =IF(ISBLANK(E132),NA(),AVERAGE(E132:E150))
which means:
“If there is not enough data for this simple moving average, then give me NA# (so excel does not plot anything) otherwise just calculate the average.”
Next just drag and copy this formula from R150 up to R19 (the first cell where it is possible to have enought data to calculate the average!)
Now all that is left is to add this moving average to the chart. It’s surprisingly tricky though.
1. Right click on the chart and select “source data”
the following screen comes up. Click on “add” and fill it in as follows. (make sure there are some numbers in column R)
2. Select OK and take a look at the chart. Things should look funny on the chart now. Don’t worry about it, just find the data series “indicator”. It should be somewhere on the chart. In my case it plotted this price moving average on the secondary axis which is volume!
Hover your mouse around the volume series looking for “indicator”. Right click on the “indicator” series and change it to primary axis from secondary axis (which will be hidden somewhere down in the volume bars if it’s on the secondary axis. It can be hard to find/click on, if it is you may want to zoom in)
Now “indicator” should be plotted up there with price, but now the price chart will be all messed up!!!!!! (see how the first bar is missing parts!)
Next you once again need to hover your mouse around the price series looking for “indicator” when indicator is visible, right click on it and select “chart type”.
The following screen comes up and just select XY (Scatter) as shown and press OK.
Now the chart will look normal again.
The last step is to properly format this scatter chart to look like a line. For the last time find the “indicator” series on the chart and right click on it. This time select “format data series” and set it as follows.
Select “OK” and the resulting indicator should like like this.
One final tip.
Use google! If you are wanting to add a stochastic indicator for example, but don’t know how to calculate it. Try the following search:
stochastic filetype:xls
This will return tons of Excel files described with the word stochastic. There is a good chance that you will find a nice example there. If you have no luck, next you should search investopedia.com, which often explains the calculations.
Recent Comments