Trades and Signals Table schema

C2 Explorer
1

If I download Mozart Trades from the website I get a line (fragment) like this:

6/6/2016 14:03:00 LONG 3000 BAX BAXTER INTERNATIONAL 44.1541 3000 6/22/2016 12:24:00 44.8836

If I download the trades from the Trades Table I get (some columns hidden for clarity):

102705172 85617966 102705169 104216503 1 2188 3000 3000 0 19696.5 19701 BAX 2016-06-06T14:03:07 2016-06-22T12:24:38 BTO

If I download the referenced signals

For entry I get:
102705169 85617966 102705172 2016-06-06T14:03:07
2016-06-06T14:03:07 BTO 900 BAX 1 0 0 0 stock 0 43.770057

For exit I get
104216503 85617966 102705172 2016-06-22T12:24:37 2016-06-22T12:24:38 STC 2000 BAX 1 0 0 1 stock 0 44.859

Nothing really lines up. Not the quantities, among trade and exit and entry signals, not the prices.

This is not an individual data bug, I run into it all over the place when I try to do a join between the trades table and the signal table. It just doesn’t work properly.

I assume (??) that the data you use internally is accurate, but the exported database schema is not very useful. [I **suspect** --but this only speculation on my part-- that internally you do some type of grouping and aggregating but this is not captured in the exported schema.]

Thanks,

Joseph

2

Let’s start slowly.

Firstly, notice that some trades are sums of sub trades.

Click on “Show AutoTrade data”:

Scroll down to your BAX example. You will see subtrades:

Secondly, trades downloaded or visible on Collective2 site includes commissions and fees.

Click on any value in the P/L column and you will see it:

Contrary, trades data downloaded from C2Explorer (C2TRADES) are meant as a source for further analysis and do not include those costs.

If you want to see commissions, please look at the example here:

https://collective2.com/c2explorer_help/html/1d9102b7-2226-4507-b764-485dd598dd26.htm#Section4

I hope the above text could explain some details and help you with further investigation.

3

Thank you very much for answering so quickly!

The data snippets in my original question were not very well formatted (apologies!). My question was not about the potential discrepancy in trade results with or without commission.

It was about the structure of the TRADES and the SIGNALS tables.

The TRADES table has a field, CloseSignalId and it is documented as “ID of the closing signal.” This, as I pointed out in my original is a little misleading as the FK-ed SIGNAL entry is only one of the signals contributing to this trade.

In my code using this ID I tried to JOIN the TRADES and the SIGNALS table but I got incorrect result. This is what I hoped to show in my original post, when I said that “Nothing really lines up. Not the quantities, among trade and exit and entry signals, not the prices.” I understand now that in order to get correct result I need to do a GROUP JOIN. (And I don’t understand the role of the Open/Close SignalId in the TRADES table, but I don’t need to understand it for what I am doing.)

Thanks again,

Joseph

[Just an aside: this took me a while to discover; you may want to rewrite the documentation slightly, like “ID of one of the closing signals.” Just a suggestion.]

4

OpenSignalId is the first signal. It opens a trade.
CloseSignalId is the last signal which makes a position flat.

Let’s stay with Mozart and investigate the trade #102705172:

TABLE = from trade in C2TRADES
        where trade.Id == 102705172
        select trade;

TABLE = from signal in C2SIGNALS
        where signal.TradeId == 102705172
  		select signal;

Hope it helps.