Hello.

I am trying to connect to the API using Excel VBA.
I am able to "Connect" to the API via TCP/IP using Excel VBA. However, I do not know how to send and receive a message.

##################
My questions
=========
Now that I am connected using TCP/IP,
1. Using Excel VBA, how do I send a message to TCP/IP, sample request
"HTX,@ES#,10,1,TESTREQUEST<CR><LF>"
2. Using Excel VBA, How do I retrieve or receive a response? Either in a text string, variable or into a Cell?
###################


I am able to do the following
1. Connect using PUTTY - TCP/IP (Telnet)
2. Address: 127.0.0.1, Port: 9100

Once connected in Putty, I can send the following message
"HTX,@ES#,10,1,TESTREQUEST<CR><LF>"

In Putty, I received the following Response

TESTREQUEST<CR><LF>,2023-04-17 04:38:48,4168.00,1,79263,4167.75,4168.00,2729085,0,0,C,
TESTREQUEST<CR><LF>,2023-04-17 04:38:48,4168.00,2,79265,4167.75,4168.00,2729085,0,0,C,
TESTREQUEST<CR><LF>,2023-04-17 04:38:48,4168.00,2,79267,4167.75,4168.00,2729085,0,0,C,
TESTREQUEST<CR><LF>,2023-04-17 04:38:48,4168.00,1,79268,4167.75,4168.00,2729085,0,0,C,
TESTREQUEST<CR><LF>,2023-04-17 04:38:48,4168.25,1,79269,4168.00,4168.25,2729086,0,0,C,
TESTREQUEST<CR><LF>,2023-04-17 04:39:00,4168.25,1,79270,4168.00,4168.25,2729087,0,0,C,
TESTREQUEST<CR><LF>,2023-04-17 04:39:00,4168.25,1,79271,4168.00,4168.25,2729087,0,0,C,
TESTREQUEST<CR><LF>,2023-04-17 04:39:00,4168.25,1,79272,4168.00,4168.25,2729088,0,0,C,
TESTREQUEST<CR><LF>,2023-04-17 04:39:00,4168.25,1,79273,4168.00,4168.25,2729088,0,0,C,
TESTREQUEST<CR><LF>,2023-04-17 04:39:00,4168.25,1,79274,4168.00,4168.25,2729088,0,0,C,
TESTREQUEST<CR><LF>,!ENDMSG!,

Over to EXCEL VBA

I downloaded the tool "Mobus activeX"
"https://www.modbustools.com/modbus_activex.html"

The VBA code used is

Mbaxp1.Connection = TCP_IP '0 = TCP/IP
Mbaxp1.IPAddr1 = 127
Mbaxp1.IPAddr2 = 0
Mbaxp1.IPAddr3 = 0
Mbaxp1.IPAddr4 = 1
Mbaxp1.TCPIPPort = 9100
Mbaxp1.Timeout = 1000 ' Read timeout
Mbaxp1.ConnectTimeout = 2000 ' TCP/IP Connection timeout
Mbaxp1.LicenseKey ("xxxx-xxxx-xxxx-xxxx-xxxx-xxxx")

e = Mbaxp1.OpenConnection

e = Mbaxp1.GetLastError

If e = 0 Then
Cells(5, 2) = "Connection success"

I am able to connect, receiving the response e=0

My questions
=========
Now that I am connected using TCp?IP,
1. Using Excel VBA, how do I send a message to TCP/IP,
2. Using Excel VBA, How do I retrieve or receive a response? Either in a text string, variable or Cell?


Attached are my screen prints of Putty and Excel VBA.

Thank you in advance and any pointers or ideas will be greatly appreciated.

Thanks,
Megabite

Hello,

I have attached my Excel file just in case.
This was downloaded and modified from
"https://www.modbustools.com/modbus_activex.html"

Apologies that my codes are "everywhere". I will clean it up once I have a solution.

Thanks again and Best Regards,
Megabite

Does you have access to Winsock in Excel VBA? That would give you the ability to connect to a socket, and read the response. I found this page on the Internet that may be helpful: https://stackoverflow.com/questions/65528189/how-to-use-winsock-control-in-excel-vba

Include \n\r when sending the command to the socket, as it needs a carriage return/line feed. The response of the IQFeed API is raw text.

---

Sincerely,
Gary Stephen
DTN IQFeed Implementation Support Specialist

Hi Gary,

Thank you for the link.

I have been able to register the DLL. (Please see screen print attached). I don't quite understand point 3.... (not sure what I have to do here.)

How do I connect to a socket, and read the response? Is it possible to get an Excel workbook with an example? Any help or hint will be greatly appreciated.

Thank you in advance,
Megabite

Based on what I read from the modbustools online help https://www.modbustools.com/mbaxp_quickstart.html, I think you just need to know how to send an IQFeed API command and read and read the response. It looks like you're connecting OK. There's probably some kind of command to send ASCII messages like

Mbaxp1.SendMessage("HTX,AAPL,5")

This is similar to how other programming languages like Python interface with the IQ feed API. Then of course you need a way to interpret the response: Step 11 in that page looks like a step in that direction,

Honestly, you'd probably get the best help from modbustools support, since they can provide you with a list of commands and a knowledge of what settings need to be in place for this kind of connection. I've personally never made IQ feed work directly with VBA before. But it should be possible if VBA has the ability, and the modbustools plugin appears to give it that.

---

Sincerely,
Gary Stephen
DTN IQFeed Implementation Support Specialist

It seems you found the QMatix tool at http://QMATIX.com and it had the functionality you needed for this project. I'll mention it here in case it is helpful to anyone else trying to do something similar.

---

Sincerely,
Gary Stephen
DTN IQFeed Implementation Support Specialist

Step 1: Don't use Excel
Step 2: If you ignored step 1, try https://excel-dna.net/

Hi Andrewm,

Thank you for your suggestion.

The support team at DTN has been really great and really patient. I really love XLQ2, as it allows me to access DTN IQFeed data via Excel. It means I can now concentrate on trading rather than coding.

I am not sure what you have against Excel for trading?
As a trader, I want access to accurate data. I also want the ability to display and manipulate the data how I like it. All options are considered and I will even look at the Moon if I have to! :-)

I personally use 3 different programs with IQFeed. (XLQ2, Ensign10, Wave59). I need Excel because it has functions and flexibility that other trading programs either don't offer it or is unable to match it.

Anyway, please keep your mind and options open. The doors you close may actually be the path to success and\or profitable trading.

Kind Regards,
Megabite

I'm slightly joking about Excel. It's a great spreadsheet but it's terrible as a realtime trading platform or any other ways people abuse it.

• Logic gets divided between formulas in cells and VBA code.
  
• You can't use version control (Git, etc) to manage development in Excel.
  
• multiple developers, traders, etc can't collaborate on an excel spreadsheet simultaneously.
  
• Tools enabling software development best-practices like unit testing frameworks do no exist for Excel.
  

Now most of what a trader does in Excel is better done using Python + Jupyter notebooks or Plotly Dash.

Why do you need to collaborate when trading? The market only exist because 2 people have opposite opinions and both are willing to commit their money to their conclusion. For version control, restoring and testing, I found a function called "Save As".

Excel allows Traders to use and test new formulas without having to go through a "scope change" and submitting a change request. That's if their developer actually understood what they want (and are capable).

Now as we are both nerds and this is why we are in a Developer's forum. I am going to let you in on a secret. This comes from Benoit Mandelbrot, a Mega Nerd who worked at IBM in the 1980s (Look up The Mandelbrot Set).

He worked out that there are fractals both in nature and in the markets. He wrote a book called, "the mis(behaviour) of markets". Basically, you are looking at the same chart whether it is by the Second, Minute, Hourly, Daily, Weekly and Monthly. When you zoom in and out, they are all part of the same fractal.

Therefore, you don't need to collaborate or use "true real time" data to make money. If the data is accurate, a couple of seconds delay is not a show stopper. You can just zoom out and apply the same technique.

The market doesn't care what program you use. All that's important is that your bank account ends up in the "Green". Even if you aren't used to "excel" in your trading! :-)