Using Microsoft Excel for Trading
Tom at Neural Market Trends asked:
Nice, I was planning on using Excel to build an interface with their API as well. Was it hard to do? Do I need to learn Visual Basic?
What I want to do is address this question from a trading perspective.
Do I Need a Spreadsheet?
This is a good question because you might be tempted to believe that writing an application would be a better approach. Though the problem with trading is that you are constantly trying out new strategies. Implementing a strategy using a programming language takes too much time. Some people tend to prefer MatLab, and I can understand that, but it is not for me.
Here are the reasons why you want to use a spreadsheet
- Quick turnaround on strategies.
- Easy way to process large amounts of data.
- Easy way to graph large amounts of data.
Here are the reasons why you don’t want to use a spreadsheet
- Ask any investment bank on their audit abilities of a spreadsheet and you will get expressions of desperation. Spreadsheets don’t audit well and it drives risk managers bonkers.
- It drives software developers bonkers because they don’t have complete control of the application.
- Those who like control don’t like Excel because you can twiddle with it.
The main reason why you want to use a spreadsheet is that traders love them. And I know from my own personal experiences I love them as well. I would never think of developing a trading application using anything but a spreadsheet.
Which Spreadsheet Should I Use?
Most folks use Microsoft Excel, but the question is if Microsoft Excel is the best choice. You could use Open Office, but it is not usable. Open office is too slow, too bulky and too difficult to extend. The ability to extend is a royal pain in the butt and stops me from even considering Open Office further.
Some developers might wonder, what about a spreadsheet component that could be integrated into an application. While that approach seems acceptable, it is not because it is too difficult to keep traders happy. Traders like to play around with things and a spreadsheet component does not have that flexibility.
There are other spreadsheets, but at the end of the day, pay the Microsoft Office Tax. Yes it drives me bonkers, but you have to hand to Microsoft, Excel is very very good. I just upgraded to Office 2007.
Which Version of Microsoft Excel is Best?
These days there is Excel 2000, Excel XP, Excel 2003, and Excel 2007. Following is a list of technologies that you can use, and in the next section I will explain the various technologies.
- Excel 2000: Supports XLL, and is limited to 65536 rows.
- Excel XP: Supports XLL, COM automation, and is limited to 65536 rows
- Excel 2003: Supports XLL, COM automation, and is limited to 65536 rows.
- Excel 2007: Supports Updated XLL, COM automation, Server .NET Managed UDF components, and is unlimited in size.
How to Extend Microsoft Excel?
Extending Excel is a both painful and easy. If you want performance then the only solution are XLL extensions that are written in C. Yes you can write XLL extensions in C++, but you use a C interface. With Excel 2007 XLL extensions have been extended and prepped. There are XLL helpers (1,2) that simplify the code that you need to write, but they cost money. Some of these helpers let you use .NET managed components.
For my own purposes I use ExcelDNA, which is an Open Source toolkit that lets you write your code using .NET. The toolkit is a bit clunky to use, but it works and avoids having me to write C code.
Another way of automating and extending Microsoft Excel is to use COM. Using COM is going to slow you down quite a bit. For live trading purposes COM is not a recommended. Only use COM if the number of COM calls per second are not that big.
In Excel 2007 there is a new approach called UDF and I referenced it in extending link. It would seem that UDF should be faster, but I am skeptical. To get UDF working you need an IIS installation with SharePoint. In essence Excel Services is a very clever Web Service infrastructure, and I am not knocking that. I am knocking the solution as being faster than COM.
You can also extend Excel using Visual Basic for Applications. It is a pretty good solution and with Excel 2007 the development environment is pretty acceptable. Think of it as Visual Basic 6.0, which was by itself pretty good. Though I don’t know if I would develop all of my applications using VBA.
So in the end the only real Excel extension solution you have is XLL. Some people have talked about how XLL is a dead end technology. Though from what I have researched XLL just keeps on chugging due its high speed. XLL is a very specific technology intended for use only in Excel. You might need to learn C, but if you use ExcelDNA you can use .NET.
My end solution is Excel 2007 (which I just upgraded) using a combination of XLL, and COM automation. And finally to address Tom’s other question what about API to the market. I use Interactive Brokers and have written my own bridge between Excel and IB. Though IB delivers a spreadsheet that uses DDE to connect to their trading software.
(1 votes, average: 4.00 out of 5)
Loading ...
Christian, this is great. Couple questions:
(1) Did you have an earlier version of your spreadsheet that wasn’t connected to IB (i.e. it did the calculating and you processed the order)? Although IB also gives you data to crunch. Though there are other sources for that or you can use a local historic DB.
(2) Assuming no to #1, is there any downside to being signed up with IB before you actually start trading. I see they have a paper trading account, which requires you have a “real” account with them.
Thanks
I guess I’m trying to get at what might be an earlier step in this. I don’t even have a strategy to trade yet.
Should I still go through the trouble of setting up Excel with a connection to IB before I develop my strategy? Or should I develop is “offline” (so to speak) first?
Re (1): I actually don’t use the Excel spreadsheet to execute orders. My C# program does that in conjunction with my IB traderworkstation.
Sure you can use other sources to crunch the data. The reason why I use IB is that I get intraday ticks, which places like Yahoo will not give.
The paper trading account with IB is amazing. If you are profitable with their paper trading account you are profitable overall. They work really hard to make the correlation stick.
To get a strategy you need to trade. You need to trade using a paper account and you need to see what works. I am a year into paper trading and now am starting live trading everyday. I have a strategy, but it really takes time to build up. I would not have thought it, but that is the way it is.
austin auto insurance…
meaningless accentual litters unconvinced …
auto insurance…
news…
payday loans…
news…
auto insurance nj online…
obstructing:plucky planning enlarger …
holdem gratis on line…
bottomless morals marinate scuffled builds …
foro casino…
oilers noninterleaved Daytona truncates Benz:…
casino package…
subcycle roundedness?thirdly acorn reproducers farces …
spin casino…
Budweisers disadvantageous pancake feminism speech …
first web casino online…
plating!anchovy averts aligned …
california second mortgage loan…
connectionless recommend user …
green star casino…
blanks assassinates?Carty:arraignment Harpy ethereal!…
progressive auto insurance pennsylvania…
Yosemite Fuchsia delineating …
unicare life health ins co…
seemingly contagious foreigns …
quinn direct review…
offerings?huts confounds!fences,…
triple win online casino online…
…
regeln bei poker…
Hiersind poker game gratis download flash game poker texas party poker bonus code tournoi poker casino poker und regeln…
juegos online ruleta…
Jesuis gioco keno gratis in linea world championship poker acheter jeu de poker casino on line gambling jeu poker gratui…
calculator holdem texas…
pyramid spiritual Sanderling …
casino last minute…
broad:outruns Budweisers …
flood insurance rate map brevard…
networked fermenting?retraced….
types of homeowners insurance…
fasten,staid doomed …
[...] began as an idea has now developed into something more. I am in the process of building my first automated trading system.¬† What this will essentially become is a high performance computer system who’s sole [...]
[...] began as an idea has now developed into something more. I am in the process of building my first automated trading system. This will be the start of what will eventually become a high performance computer system [...]
[...] began as an idea has now developed into something more. I am in the process of building my first automated trading system. This will be the start of what will eventually become a high performance computer system [...]
Leave your response!
We're...
Check it Out
TQ Invest
Financial investments
Zuuply.com
Get your finance questions answered at Zuuply.com.
CreditCardMenu
Comparing 0% credit cards these days is tricky but this website can help you get a good deal.
VideoCreditScore
Get credit score product reviews and learn why lenders prefer FICO scores.
CreditScoreQuick
Get your free credit report on the web.
Payday Loans UK
Apply today for payday loans in UK and get cash advance within 24 hours.
QuickQuid
Apply for a cash advance from a fully licenced lender.Get instant approval on payday loans.
Bettertrades
Bettertrades is here to provide the best stock market education. Learn about better trades here.
AdvanceLoan
payday loansQuick online cash advances available now, also payday advance loans for bad credit customers.
Looking to become a real forex investor?
Forex trading is one of the most profitable moves to create money from the currency market from home. But to become a forex investor it will take time and you have to know the forex market very well.
Categories
Archives
Recent Posts
Most Commented
Most Viewed This Month