VBA (Excel) as a command line console app

[M.W.]

That is a library not an integration. Aside the point.

It can be done, but it's a bad hack. But go ahead. Good luck algo trading with VBA and excel.

There are YouTube vids on the subject two years old. This one is one year old, which i reviewed a year ago:

I'm not trying (anymore) to implement Websockets in VBA. But any REST API is totally doable so i'm not sure why you think it can't be done.

 

[Good1]

That is a library not an integration. Aside the point.

It can be done, but it's a bad hack. But go ahead. Good luck algo trading with VBA and excel.

Well here is somebody that is doing it:

https://www.youtube.com/live/8thDS5EIADo?si=Mc9o8nQWA57ysva3

He's doing some live demos on like, 15 second data or something, to show it has speed. And i believe he is still using mostly formulas for a lot of his calculations, not even VBA. He teamed up with a developer to implement the API aspects of his bot. I'm not sure if they are using Websockets. Probably they are.

Anyway, Excel has an HTTP library that can be referenced for REST API implementation. It turns out that this is a Windows library that someone, probably Microsoft, wrapped especially for use with VBA. Very closely associated with that library is a library for Windows implementation of Websockets. Presuming these libraries are also in C++, they would technically be as fast as Python wrapped C++ ??? Anyway, Microsoft made a decision not to wrap their Websockets library for use with VBA, probably because they never had that much faith in VBA's ability to really keep up with the speed, or they had other products to better focus on. Pretty much everyone else has agreed since there is paltry little example code to get Websockets up and running in VBA.

 

[M.W.]

For goodness sake, drop your hard on for web sockets. You don't need to concern yourself with such implementation details when using Excel. If it suits you use a ready-made Excel add-in that allows you to feed market data onto your spreadsheet. Done. If this is what suits your needs go ahead but that has nothing to do with algorithmic trading.

Just because you see something on YouTube does not mean it's a good solution.

Well here is somebody that is doing it:

https://www.youtube.com/live/8thDS5EIADo?si=Mc9o8nQWA57ysva3

He's doing some live demos on like, 15 second data or something, to show it has speed. And i believe he is still using mostly formulas for a lot of his calculations, not even VBA. He teamed up with a developer to implement the API aspects of his bot. I'm not sure if they are using Websockets. Probably they are.

Anyway, Excel has an HTTP library that can be referenced for REST API implementation. It turns out that this is a Windows library that someone, probably Microsoft, wrapped especially for use with VBA. Very closely associated with that library is a library for Windows implementation of Websockets. Presuming these libraries are also in C++, they would technically be as fast as Python wrapped C++ ??? Anyway, Microsoft made a decision not to wrap their Websockets library for use with VBA, probably because they never had that much faith in VBA's ability to really keep up with the speed, or they had other products to better focus on. Pretty much everyone else has agreed since there is paltry little example code to get Websockets up and running in VBA.

 

[Good1]

For goodness sake, drop your hard on for web sockets. You don't need to concern yourself with such implementation details when using Excel. If it suits you use a ready-made Excel add-in that allows you to feed market data onto your spreadsheet. Done. If this is what suits your needs go ahead but that has nothing to do with algorithmic trading.

Just because you see something on YouTube does not mean it's a good solution.

I looked into that. The primo add-in for crypto data is called Cryptosheets. And i tried it out. There is a free version and a subscription version. I had issues with the free version and so was not ready or willing to go subscription. First of all, the free version is cleverly coded that you cannot automate a regular fetching of OHLC data. You must request it by typing into a cell, through the keyboard. That level of coding suggests they built the add-in at a much higher level, in another language, on another platform. I know that you can make add-ins for Excel using Visual Studio and Visual Basic and probably any other language there through something called VSTO. I know because i've done it. And this is probably how Cryptosheets was able to to implement something that looks like websockets, which will feed a cell with continually updating price info. But that is available from just one exchange, and/or source, "ExRate", whatever that is. Rest API requests could come from a lot more sources, but not automated for the free version. The big issue, though, was that the add-in would periodically, and randomly disappear from the ribbon, and would be harder than hell to re-install. Not a problem with the online version of Excel, but a problem for the desktop version. So at about $30? dollars a month, you are just a "user", completely at the mercy of developers. Why should anyone who can code settle for that? I currently fetch OHLC data at the maximum rate limit, for example, 200 per minute, from anyone i want to, and am building databases with one minute granularity, limited only by exchange rate limits. It looks like i can grab 1000 per minute from Bybit (per symbol, i believe) but i'm not pushing my luck because their API states 200. Binance keeps close tabs on rate limits as they are very verbose about it.

Like i said, one reason i am going forward with VBA is because i realized i did not need websockets, only as a programming challenge, which i am postponing. I have one minute granularity for an algorithm that will be fueled with 8 hour intervals (bars) and thereabouts. And not just on the standard hour strikes, but in between standard strikes. Basically custom bar intervals. ( I want to be on 30 minute, and 9 minute bars, but can't overcome trading fees unless using cheap limit orders, which would then require a Websockets kind of datastream.)

The solution, to maintaining a smooth equity curve (small predictable drawdowns), then, is to go with market orders on a slower time frame, and to have as many uncorrelated positions open at the same time as possible. This is really where VBA (or any desktop native language) can beat proprietary platforms such as MetaTrader, and is why one trader (see YouTube) sent all his Forex data down from MetaTrader down to Excel to be processed with VBA for all the heavy lifting, and then sent back up to MetaTrader for order execution at his broker(s), with kick-ass results. With a 1.7 profit factor, if i take every trade, i am nowhere near his results. The solution, then is to go with many positions, as uncorrelated as possible.

Where Excel might have a problem is threading, and maybe memory, where, if it's not managed well enough, could freeze up the app. Might have to employ some Do Events sort of thing every once in a while. While it is performing one task, Excel might not do well on others (If you have data in a lot of cells, which i don't, Excel could even have a problem opening). So one is relying on it's sequential speed, to get everything done in a reasonable time, that and one's hardware. For example, i can only fetch from one exchange at a time, and put in orders at one exchange at a time, one symbol at a time. The speed of analysis (of data), is really not the concern, unless we are talking about 1000 stocks like every 15 seconds. Maintaining 30 positions every minute is not that difficult.

I don't think Excel is exactly the toy that you think it is. The command line console app idea i've offered here, with the Application.OnTime function, and the referenceable HTTP library for Rest API, with no reliance on cells, ranges, or formulas (use CSV and TXT files instead)... is more like a solution than a hack. Not all algorithms require real time sentiment data, or neural network AI b.s.

 

[d08]

I'm very aware of that. I chose not to go that route as well. And that route is still open, even with a finished VBA app since the concepts are similar: REST API, classes, time loop, dictionary's, collections, arrays, loops, read, write, splitting strings, and what have you. Then speed. Python is not known as a race track though it might (don't know) be faster than VBA. If speed is needed, it's easier to upgrade CPU and drive and RAM power than to come up to speed learning a new language. If Python would make it easier to implement Websockets that would be a time to consider integration, otherwise I would choose Python and/or C# (console ) on the Visual Studio IDE. Maybe Python could help with charting? That is all I'm using Excel for anymore. The editor will allow both languages in one program .

Python can do all of that, what's difficult about websockets in Python?
Nearly no-one here will be hindered by the speed of Python, we're not competing on the sub-second space. Excel is fine for making your monthly budget but nothing much else.

I've used IB's Excel implementation first with DDE (terrible), then with ActiveX - regular issues, often with Excel updates on my machine that broke things. I've also used Excel to get 1min data from BarChart, slow and buggy.

I can't imagine someone using Excel in 2023 for serious trading. You're much better learning Python or even C# (somewhat locked into MS sphere). If you want to focus on the future and speed is critical then Rust. Invest your time wisely and avoid hacky solutions that only work for the short-term.

 

[d08]

...The big issue, though, was that the add-in would periodically, and randomly disappear from the ribbon, and would be harder than hell to re-install. Not a problem with the online version of Excel, but a problem for the desktop version.

Issues like this are typical for Excel. It's not suitable for anything time critical. With a real programming solution, at least it will work until you change the code, update something or the broker makes changes.

 

[Good1]

Python can do all of that, what's difficult about websockets in Python?
Nearly no-one here will be hindered by the speed of Python, we're not competing on the sub-second space. Excel is fine for making your monthly budget but nothing much else.

I've used IB's Excel implementation first with DDE (terrible), then with ActiveX - regular issues, often with Excel updates on my machine that broke things. I've also used Excel to get 1min data from BarChart, slow and buggy.

I can't imagine someone using Excel in 2023 for serious trading. You're much better learning Python or even C# (somewhat locked into MS sphere). If you want to focus on the future and speed is critical then Rust. Invest your time wisely and avoid hacky solutions that only work for the short-term.

I've not suggested Python can't handle websockets. I've just implied that by the time you get down to a Python, or a C# implementation of websockets, you may as well migrate all your code over to that language, onto whichever platform supports them best.

Thanks for everyone's suggestions, i know you're just trying to help. I just made a hard decision to finish my first iteration (this is actually my second iteration; the first proof of concept is already deprecated. This one is conceived to be valuable enough to migrate, for the most part, later on) of a trading bot in the language, and on the platform where i have had the most invested.

There was a time when Michael Saylor was offering his Microstrategy software as a VBA solution. I wish i could relocate the podcast where he admitted this, and that he was under pressure to use some other language or platform at the time. But it was working.

I want to make gradual improvements, in other languages, on other platforms, after i have proof of concept, and a jingle in the pocket, to learn while earning. By staying course, i could arrive there two to three months sooner at an AWS installation running 24/7.

I'll report here if for some reason Excel can't stay running 24/7, or would need an additional app to check on it every minute to make sure it is running. But if it stays running, it's the cleverness of the code, if any, that will produce the profit, not as much the language or the platform.

I can understand the concept of limitations. For example, to achieve scalability, one should not even begin to code with Excel formulas in cells and ranges. Should not waste time, and go straight to an actual language, with classes, arrays, collections, dictionarys, enums, loops, libraries, examples, references, naming conventions, variable typing, initializing, reading, writing, pointing vs copying (byref vs byval), functions vs subs, string manipulation, intellisense (a good editor) and what not. But the difference between VBA and Visual Basic on Visual Studio, or C# or Python on Visual Studio, is not as big a difference as there is between Excel formulas and an actual language. The same principles of computing and programming apply and you have to forge a clever solution by dint of hard work no matter where you are. Organization of code toward scalability is a thing you have to master no matter what or where.

I've set out to implement just my own algorithm, for my own use, and have found that the algorithm is the easy part compared to everything else involved in making it run in the real world with any degree of scale and flexibility, anywhere i want it to run. Turning VBA into a console app that calls itself every minute to build a database and check positions is a solution i've stumbled upon to get there sooner than later.

 

[newwurldmn]

You can run command line instructions in vba.

I’ve done that for computational intense processes. I write the code in Java and then use cmd in vba to call it. This way I can keep the excel interface.

 

[Good1]

You can run command line instructions in vba.

I’ve done that for computational intense processes. I write the code in Java and then use cmd in vba to call it. This way I can keep the excel interface.

There are people who run their entire computers only through a command line. I learned to appreciate it when toying with a retro computer some years ago, an 8 bit Z80, floppy disks, and a green phosphorus screen. It ran CP/M. Before Microsoft had .batch (.bat) files, Digital Research used .submit (.sub) files for little scripts that would help you automate as many tasks as possible from the command line. I did a project once where i used a .sub file to open Multiplan (Microsoft's earliest spreadsheet), run a program, make some inputs, wait for outputs, close, and open up another program and repeat.

Find attached a BATCH script i wrote that uses your modern Windows command window as a super quick logging app. Below is the menu screen that describes how to use it. It will write to two different logs you might want to keep (ie. personal, project), or any other .TXT file you want to write to. It stamps your notes by date and time, and then makes it easy to search through and find notes by showing all sentences with the keyword you are looking for.

To use it, rename/resave the .txt file attached as a .batch file, and point your command window at it so when your window opens, the "command line" is ready to accept input by default. Oh, but first change the color of your font to phosphorus green . Not exactly Powershell but i have used this SO much over a decade now.

 

[Good1]

The following code will, when you open this Excel Workbook, will minimize the main window and put the VBA editor up front. In this case, the editor opens up to a class module called clsCommands, executes a subroutine called "About", and prints the name of the Subroutine i am currently working on. If i put the cursor next to that word, to the right, and ENTER, it will execute that routine.

This is a great way to get started if you are doing a lot of work in the VBA editor.