Excel still slow with SP2, despite claims of improved charting.

I have high hopes for performance improvements each time MSFT releases an new version or service pack.  This time the claim with Office 2007 service pack 2 that had me curious was:

Improves the charting mechanism in Excel 2007. This includes better parity with Office 2003, improved robustness, and targeted performance improvement”

Benchmark.xls is a file I created to keep track of Excel’s performance in various O/S and versions. Benchmark.xls makes heavy use of Excel’s charting capabilities and manipulates data in a way typical for handling live market data/trading.

With SP2, Excel still took roughly two and a half hours to complete what takes Office 2003 five minutes.

Office 2003       : Backtest results: 00:04:56
Office 2007 SP1: Backtest results: 02:29:48
Office 2007 SP2: Backtest results: 02:35:38

sp2

See Benchmark.xls for more detailed results or to download benchmark.xls.

MACD

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.

MACD example in excel.

Excel question


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.

chartq1

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.

chartq21

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.

chartq4

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.

chartq5


How to post your ATS trades in twitter using Excel

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”