# Excel VBA code or URLs for trading, position checking etc
**URL:** https://forums.collective2.com/t/excel-vba-code-or-urls-for-trading-position-checking-etc/8108
**Category:** C2 Software Development
**Created:** [June 23, 2015, 7:27am UTC](https://forums.collective2.com/t/excel-vba-code-or-urls-for-trading-position-checking-etc/8108 "2015-06-23T07:27:35Z")
**Posts on this page:** 17
**Page:** 1
### Author:  [@Cameron](https://forums.collective2.com/u/Cameron)
#### Post date: [June 23, 2015, 7:27am UTC](https://forums.collective2.com/t/excel-vba-code-or-urls-for-trading-position-checking-etc/8108/1 "2015-06-23T07:27:35Z")
Hi, does anyone know if there is a list somewhere of URL examples we can use for trading/position checking etc. I am looking for examples such as an order to liquidate all open positions at market or a request for the webpage to return text that lists a systems open positions.
The only example that I know works is the following which is used to place a simple order:
[https://www.collective2.com/cgi-perl/signal.mpl?cmd=signal&systemid=84690231&pw=password&instrument=future&action=BTO&quant=2&symbol=MHIM5](https://www.collective2.com/cgi-perl/signal.mpl?cmd=signal&systemid=84690231&pw=password&instrument=future&action=BTO&quant=2&symbol=MHIM5)
Indeed it would be even better if someone could direct me to a page that shows how to do this with Excel VBA code without the need to hit up Internet Explorer at all. Otherwise perhaps someone could kindly paste that code here.
Thank you.
---
### Author:  [@Cameron](https://forums.collective2.com/u/Cameron)
#### Post date: [June 27, 2015, 1:26am UTC](https://forums.collective2.com/t/excel-vba-code-or-urls-for-trading-position-checking-etc/8108/2 "2015-06-27T01:26:04Z")
Has anyone converted the JSON code at [https://www.collective2.com/api-signal-entry](https://www.collective2.com/api-signal-entry) to VBA code? Please share, sharing is caring, and it creates good karma for your future trading success 😄
---
### Author:  [@NQ17](https://forums.collective2.com/u/NQ17)
#### Post date: [June 27, 2015, 4:16am UTC](https://forums.collective2.com/t/excel-vba-code-or-urls-for-trading-position-checking-etc/8108/3 "2015-06-27T04:16:43Z")
Hello,
I am really interested about this too.
Maybe I will try to come up with something in the next few days (if there is some way it can be done too, which I have no idea so far).
Right now I use excel to maintain portfolio strategies and do the asset allocations.
I currently use some direct webservice to get current quotes in excel or some VBA to get quotes (open/close) from yahoo api.
However I have some small issues with this.
One of these is that yahoo, like most sites on internet, reports the consolidated open/close stock quotes.
But I am interested in the correct exchange quotes of the securities, which is the one to use when you trade at open or close like I do, and direct your order directly to the exchange (can be done with IB), and C2 seems to provide it in the prices I get on C2, from what I have seen so far.
---
### Author:  [@NQ17](https://forums.collective2.com/u/NQ17)
#### Post date: [July 3, 2015, 4:25am UTC](https://forums.collective2.com/t/excel-vba-code-or-urls-for-trading-position-checking-etc/8108/4 "2015-07-03T04:25:19Z")
Hello,
So I found some way to do it, with the Restlibrary.
All credit should go to the guys that did this : [http://ramblings.mcpher.com/Home/excelquirks/json](http://ramblings.mcpher.com/Home/excelquirks/json)
You can download their complete excel file with all the examples and the Restlibrary here:
[http://ramblings.mcpher.com/Home/excelquirks/downlable-items/cDataSet.xlsm](http://ramblings.mcpher.com/Home/excelquirks/downlable-items/cDataSet.xlsm)
Here is an example on how to use it with a macro that will get me the current opened positions (command requestTrades in the Signal API v2).
Of course you need to replace “XXX” and “YYY” by your own apiKey and systemId
Sub PopulateC2RequestTradesOpened()
Dim sURL As String
Dim sJson As String
Dim xmlDoc As New DOMDocument
Dim sEnvlength As Integer
Dim responseText As String
Dim sC2RequestTrades As String
Set objHTTP = New MSXML2.XMLHTTP
’ URL for requestTrades command
sURL = “[https://collective2.com/world/apiv2/requestTrades](https://collective2.com/world/apiv2/requestTrades)”
'JSON
sJson = sJson & "{"
sJson = sJson & " ““apikey””: ““XXX””,"
sJson = sJson & " ““systemid””: ““YYY””“
sJson = sJson & " }”
objHTTP.Open “POST”, sURL, False
objHTTP.SetRequestHeader “Content-Type”, "application/json"
objHTTP.Send (sJson)
xmlDoc.LoadXML (objHTTP.responseText)
sC2RequestTrades = objHTTP.responseText
’ Change the quotes format
sC2RequestTrades = Replace(sC2RequestTrades, “”"", “’”)
'Display the info retrieved from C2
MsgBox sC2RequestTrades
'Now use Restlibrary, then we can parse the JSON
Dim cj As cJobject, rout As Range, ds As cDataSet, job As cJobject
’ C2RequestTrades must be defined in the Restlibray Module in Function createRestLibrary()
’ restQuery will not do the query to C2 here, it has been done before, we only use it to create the JSON objects
With restQuery(“C2”, “C2RequestTrades”, , , , , , False, , , True, sC2RequestTrades)
’ clear the sheet
wholeSheet(“C2”).Cells.ClearContents
’ add the headings by getting every single key in the top level array
```
' For Each job In .datajObject.children
' For Each cj In job.children
' if we dont already have this heading then add it
' If rout Is Nothing Then
' Set rout = firstCell(wholeSheet("C2"))
' rout.Value = cj.key
' Else
' If (cleanFind(makeKey(cj.key), rout) Is Nothing) Then
' Set rout = rout.Resize(, rout.columns.count + 1)
' lastCell(rout).Value = cj.key
' End If
' End If
' Next cj
'Next job
```
’ Or just Add the headings we want to be displayed
Set rout = firstCell(wholeSheet(“C2”))
rout.Value = “symbol”
Set rout = rout.Resize(, rout.columns.count + 1)
lastCell(rout).Value = “trade\_id”
Set rout = rout.Resize(, rout.columns.count + 1)
lastCell(rout).Value = “open\_or\_closed”
Set rout = rout.Resize(, rout.columns.count + 1)
lastCell(rout).Value = “openedWhen”
Set rout = rout.Resize(, rout.columns.count + 1)
lastCell(rout).Value = “quant\_opened”
Set rout = rout.Resize(, rout.columns.count + 1)
lastCell(rout).Value = “opening\_price\_VWAP”
Set rout = rout.Resize(, rout.columns.count + 1)
lastCell(rout).Value = “closedWhen”
Set rout = rout.Resize(, rout.columns.count + 1)
lastCell(rout).Value = “closing\_price\_VWAP”
Set rout = rout.Resize(, rout.columns.count + 1)
lastCell(rout).Value = “PL”
’ now make a dataset of the headings
Set ds = New cDataSet
ds.populateData rout
’ Use some date to filter the positions if needed
Dim sYesterday, sToday As String
sToday = Format(Date, “yyyy-MM-dd”)
sYesterday = Format(DateAdd(“d”, -1, Date), “yyyy-MM-dd”)
'add the data values
For Each job In .datajObject.children
```
' Here we only keep "Open" positions and discard the other ones
If job.cValue("open_or_closed") = "closed" Then 'Or left(job.cValue("openedWhen"), 10) <> sToday Then ' Just keep positions opened today
job.remove
Else
For Each cj In job.children
If cj.key = "symbol" Or cj.key = "trade_id" Or cj.key = "open_or_closed" Or cj.key = "openedWhen" Or cj.key = "quant_opened" Or cj.key = "opening_price_VWAP" Or cj.key = "closedWhen" Or cj.key = "closing_price_VWAP" Or cj.key = "PL" Then
With ds.headingRow.exists(cj.key)
.where.Offset(job.childIndex).Value = cj.Value
End With
End If
Next cj
End If
```
Next job
’ clear up dataset
ds.tearDown
’ clear up restquery
.tearDown
End With
End Sub
---
### Author:  [@Cameron](https://forums.collective2.com/u/Cameron)
#### Post date: [July 3, 2015, 4:40am UTC](https://forums.collective2.com/t/excel-vba-code-or-urls-for-trading-position-checking-etc/8108/5 "2015-07-03T04:40:22Z")
Thanks very much, but wow that’s pretty complex. What about an example of a few simple lines of VBA code to send an order?
---
### Author:  [@NQ17](https://forums.collective2.com/u/NQ17)
#### Post date: [July 3, 2015, 5:26am UTC](https://forums.collective2.com/t/excel-vba-code-or-urls-for-trading-position-checking-etc/8108/6 "2015-07-03T05:26:50Z")
If you only need to send a query to C2 but don’t care about parsing or displaying the answer, then below is just an example to send the requestTrades command to C2 and get the response and display it in a message box.
You do not need any additional library or anything for this.
Just change the URL and the JSON string format to suit what you want to do according to the command you want to use.
Sub C2RequestTrades()
Dim sURL As String
Dim sJson As String
Dim xmlDoc As New DOMDocument
Dim responseText As String
Set objHTTP = New MSXML2.XMLHTTP
’ URL for requestTrades command
sURL = “[https://collective2.com/world/apiv2/requestTrades](https://collective2.com/world/apiv2/requestTrades)”
'JSON for requestTrades command
sJson = sJson & "{"
sJson = sJson & " ““apikey””: ““XXX””,"
sJson = sJson & " ““systemid””: ““YYY””“
sJson = sJson & " }”
objHTTP.Open “POST”, sURL, False
objHTTP.SetRequestHeader “Content-Type”, "application/json"
objHTTP.Send (sJson)
xmlDoc.LoadXML (objHTTP.responseText)
'Display the info retrieved from C2
MsgBox objHTTP.responseText
---
### Author:  [@NQ17](https://forums.collective2.com/u/NQ17)
#### Post date: [July 3, 2015, 5:49am UTC](https://forums.collective2.com/t/excel-vba-code-or-urls-for-trading-position-checking-etc/8108/8 "2015-07-03T05:49:03Z")
Or if you simply want to send a buy order for example, you do not even need VBA.
You can use WEBSERVICE and directly input this into your cell,after updating system\_id and password :
=WEBSERVICE(“[http://www.collective2.com/cgi-perl/signal.mpl?cmd=signal&systemid=system\_id&pw=password&instrument=stock&action=BTO&quant=1200&symbol=IBM&duration=DAY](http://www.collective2.com/cgi-perl/signal.mpl?cmd=signal&systemid=system_id&pw=password&instrument=stock&action=BTO&quant=1200&symbol=IBM&duration=DAY)”)
Be carefull, if you validate the cell, it will send the order and you will receive the answer from C2 into the cell.
I tried it for one of my system and got this answer (not enough cash):
“
error
Unable to BTO 1200 IBM - Current account cash is $19,117.50; proposed trade requires cash of $99,054.00(code=pre)
”
---
### Author:  [@Cameron](https://forums.collective2.com/u/Cameron)
#### Post date: [July 3, 2015, 5:53am UTC](https://forums.collective2.com/t/excel-vba-code-or-urls-for-trading-position-checking-etc/8108/9 "2015-07-03T05:53:49Z")
Can this webservice function be used in VBA? Currently I’m using VBA to open up IE and point to the URL. My desire is to simple run a few lines of code in VBA without having to open IE or without having to write 25 lines of VBA code as above. For example can we use something like this:
Sub SendOrder
WEBSERVICE(“[http://www.collective2.com/cgi-perl/signal.mpl?cmd=signal&systemid=system\_id&pw=password&instrument=stock&action=BTO&quant=1200&symbol=IBM&duration=DAY](http://www.collective2.com/cgi-perl/signal.mpl?cmd=signal&systemid=system_id&pw=password&instrument=stock&action=BTO&quant=1200&symbol=IBM&duration=DAY)”)
End Sub
---
### Author:  [@NQ17](https://forums.collective2.com/u/NQ17)
#### Post date: [July 3, 2015, 6:04am UTC](https://forums.collective2.com/t/excel-vba-code-or-urls-for-trading-position-checking-etc/8108/10 "2015-07-03T06:04:52Z")
WEBSERVICE is an excel Function, not to be used in VBA.
In VBA, you still need to use a few line of codes.
So, it will be like this:
Sub SendOrder()
Dim sURL As String
Dim xmlDoc As New DOMDocument
Dim responseText As String
Set objHTTP = New MSXML2.XMLHTTP
sURL = "[http://www.collective2.com/cgi-perl/signal.mpl?"](http://www.collective2.com/cgi-perl/signal.mpl?%22)
objHTTP.Open “POST”, sURL, False
objHTTP.SetRequestHeader “Content-type”, "application/x-www-form-urlencoded"
objHTTP.Send (“cmd=signal&systemid=system\_id&pw=password&instrument=stock&action=BTO&quant=200&symbol=IBM&duration=DAY”)
xmlDoc.LoadXML (objHTTP.responseText)
'Display the info retrieved from C2
MsgBox objHTTP.responseText
End Sub
---
### Author:  [@Cameron](https://forums.collective2.com/u/Cameron)
#### Post date: [July 3, 2015, 6:11am UTC](https://forums.collective2.com/t/excel-vba-code-or-urls-for-trading-position-checking-etc/8108/11 "2015-07-03T06:11:49Z")
NeoQuant, you are very kind for all your help. I can’t quite get this to work though. It stops on the line, “Dim xmlDoc As New DOMDocument” and says Not Defined. My guess is that I need an Excel COM Add-in? How can I get that Add-in? Note I am using Excel 2007.
---
### Author:  [@NQ17](https://forums.collective2.com/u/NQ17)
#### Post date: [July 3, 2015, 6:20am UTC](https://forums.collective2.com/t/excel-vba-code-or-urls-for-trading-position-checking-etc/8108/13 "2015-07-03T06:20:37Z")
what you found is actually quite similar to what I sent to you.
- use the URL with the “.Open” command
- use the keywords and values in the “.Send” command
You need to enable HTTP and XML.
in VBA, Tools-\>References
check the references that you need (should be something like HTTP service and also XML)
---
### Author:  [@Cameron](https://forums.collective2.com/u/Cameron)
#### Post date: [July 3, 2015, 6:36am UTC](https://forums.collective2.com/t/excel-vba-code-or-urls-for-trading-position-checking-etc/8108/14 "2015-07-03T06:36:38Z")
Oh yeah, you got it!
As you say I had to enable VBA-\>Tools-\>References and tick the boxes for:
Microsoft WinHTTP Services
Microsoft XML
---
### Author:  [@Cameron](https://forums.collective2.com/u/Cameron)
#### Post date: [July 6, 2015, 10:30am UTC](https://forums.collective2.com/t/excel-vba-code-or-urls-for-trading-position-checking-etc/8108/15 "2015-07-06T10:30:00Z")
Hi everyone, some useful code here which can be added to the bottom of Neo’s above. This will capture the C2 return message and save it to a text file:
Dim fso As New FileSystemObject
Dim stream As TextStream
Set stream = fso.CreateTextFile(FilePathAndName, True)
stream.WriteLine objHTTP.responseText
stream.Close
This works great but I’d also like to capture the C2 return message into either a cell or a block of cells. I tried Range(“A1”).Value = objHTTP.responseText but it only picked up the first line of the return message, but nothing else. So I tried Range(“A1:A10”) but it just repeated over and over again. Anyone got any idea how to do it? Again, looking for the simplest code possible please.
---
### Author:  [@NQ17](https://forums.collective2.com/u/NQ17)
#### Post date: [July 6, 2015, 12:20pm UTC](https://forums.collective2.com/t/excel-vba-code-or-urls-for-trading-position-checking-etc/8108/16 "2015-07-06T12:20:28Z")
Maybe the complete answer is displayed in the cell, but you cannot see it as the cell might be too small.
To make sure that you have an issue or not, simply select the cell and do a “copy”, then open a file editor (notepad, wordpad, whatever…) and do “paste”: do you see the complete message?
Additionally, you can also want to display C2 return message into the active cell for simplicity.
Add this line to your code: ActiveCell.Value = objHTTP.responseText
Then, select the cell where you want to display the output, click you macro button to generate your order, and the message will be displayed in this cell.
---
### Author:  [@NQ17](https://forums.collective2.com/u/NQ17)
#### Post date: [July 7, 2015, 6:35am UTC](https://forums.collective2.com/t/excel-vba-code-or-urls-for-trading-position-checking-etc/8108/17 "2015-07-07T06:35:14Z")
Additionally, if you want to display in excel the information of a buy/sell signal that you have already sent to C2 and for which you have the corresponding Signalid, here is a simple macro to do it:
Sub GetC2SignalInformation()
’ Usage: Select a cell with a valid SignalId from C2, then run this macro
’ The Price and process time will be displayed in the 2 cells on the right of the one containing the SignalId
Dim sURL As String
Dim sCommand As String
Dim xmlDoc As New DOMDocument
Dim responseText, sQuantity As String
Dim nStart as inTeger, nEnd As Integer
Dim sSystem as String, sPwd as String, sSignal As String, sEmail As String
sSystem = "XXX"
sPwd = "YYY"
sSignal = ActiveCell.Value
sEmail = “ZZZ”
’ Start processing
ActiveCell.Offset(0, 2).Value = "Getting info for SignalId " & sSignal
Set objHTTP = New MSXML2.XMLHTTP
sURL = "[http://www.collective2.com/cgi-perl/signal.mpl?"](http://www.collective2.com/cgi-perl/signal.mpl?%22)
sCommand = “cmd=signalstatus&signalid=” & sSignal & “&pw=” & sPwd & “&c2email” & sEmail
objHTTP.Open “POST”, sURL, False
objHTTP.SetRequestHeader “Content-type”, "application/x-www-form-urlencoded"
objHTTP.Send (sCommand)
xmlDoc.LoadXML (objHTTP.responseText)
’ display the answer
MsgBox objHTTP.responseText
’ Extract and display the trade price
nStart = InStr(objHTTP.responseText, “``”)
nEnd = InStr(objHTTP.responseText, “``”)
sPrice = Mid(objHTTP.responseText, nStart + 12, nEnd - nStart - 12)
If sPrice = “0” Then
ActiveCell.Offset(0, 1).Value = "Not yet Filled"
Else
ActiveCell.Offset(0, 1).Value = sPrice
End If
’ Extract and display the Trade date/time
nStart = InStr(objHTTP.responseText, “``”)
nEnd = InStr(objHTTP.responseText, “``”)
sWhen = Mid(objHTTP.responseText, nStart + 12, nEnd - nStart - 12)
If sWhen = “0” Then
ActiveCell.Offset(0, 2).Value = "Working…"
Else
ActiveCell.Offset(0, 2).Value = Left(sWhen, Len(sWhen) - 4)
End If
End Sub
---
### Author:  [@Cameron](https://forums.collective2.com/u/Cameron)
#### Post date: [July 10, 2015, 6:15am UTC](https://forums.collective2.com/t/excel-vba-code-or-urls-for-trading-position-checking-etc/8108/18 "2015-07-10T06:15:51Z")
Very nice, works well, thank you.
---
### Author:  [@Cameron](https://forums.collective2.com/u/Cameron)
#### Post date: [September 5, 2015, 8:55pm UTC](https://forums.collective2.com/t/excel-vba-code-or-urls-for-trading-position-checking-etc/8108/19 "2015-09-05T20:55:51Z")
Anyway to incorporate the “park until” functionality in this?