Trade - Place Order

Place a trade/order with Ally-Invest (formerly TradeKing), using SQL Server

Prerequisites:

See SQLHTTP easy setup for Ally Invest (formerly TradeKing)
See API Call documentation for parameter values and other information
Create the stored procedure documented below

Stored Procedure:


CREATE PROCEDURE [dbo].[usp_AllyInvest_Order_Create](
		@Account varchar(20),
		@Ticker varchar(20),
		@Quantity int,
		@TimeInForce varchar(20),
		@Type varchar(20),
		@Price numeric(10,2),
		@SideOfMarket varchar(20),
		@SecurityType varchar(20),

		@TradingPassword varchar(50) = NULL,
		@OverrideWarnings bit = 0,

		@Response nvarchar(MAX) OUTPUT,
		@StatusCode int OUTPUT,
		@StatusDescription nvarchar(MAX) OUTPUT)
AS

DECLARE @TmInForce char(1)
DECLARE @Typ char(1)
DECLARE @Px varchar(15)
DECLARE @StopPx varchar(15)
DECLARE @Side char(1)
DECLARE @AcctTyp char(1)
DECLARE @SecTyp varchar(3)

SET @Typ =	CASE @Type
			WHEN 'Market' THEN '1'
			WHEN 'Limit' THEN '2'
			WHEN 'Stop' THEN '3'
			WHEN 'Stop Limit' THEN '4'
			WHEN 'Trailing Stop' THEN 'P'
		END

SET @TmInForce =	CASE 
				WHEN @Typ = '1' THEN '0'
				WHEN @TimeInForce = 'Day' THEN '0'
				WHEN @TimeInForce = 'GTC' THEN '1'
				WHEN @TimeInForce = 'Market' THEN '7'
			END

IF @Typ IN ('2', '4') AND @Price > 0
	BEGIN
		SET @Px = CONVERT(varchar(15), @Price)
	END
ELSE
	BEGIN
		SET @StopPx = CONVERT(varchar(15), @Price)
	END

SET @Side =	CASE @SideOfMarket
			WHEN 'Buy' THEN '1'
			WHEN 'Sell' THEN '2'
			WHEN 'Short' THEN '5'
			WHEN 'Cover' THEN '1'
		END

IF @SideOfMarket = 'Cover'
	BEGIN
		SET @AcctTyp = '5'
	END

SET @SecTyp =	CASE @SecurityType
			WHEN 'Stock' THEN 'CS'
			WHEN 'Option' THEN 'OPT'
		END
				 
DECLARE @URL nvarchar(MAX)
DECLARE @HTTPSessionID uniqueidentifier

SET @URL = 'https://api.tradeking.com/v1'

EXEC SQLHTTP.net.URLBuilder   @URL OUTPUT,
			      'TradeKing',
			      'accounts', @Account,
			      'orders.xml'

DECLARE @FIXML varchar(MAX)

SELECT @FIXML	= '<?xml version="1.0" encoding="UTF-8"?>'
		+ '<FIXML xmlns="http://www.fixprotocol.org/FIXML-5-0-SP2">' 
		+ (	SELECT	@TmInForce AS "@TmInForce", 
					@Typ AS "@Typ", 
					@Px AS "@Px",
					@StopPX AS "@StopPx",
					@Side AS "@Side", 
					@AcctTyp AS "@AcctTyp",
					@Account AS "@Acct",
					(	SELECT	@SecTyp AS "@SecTyp",
								@Ticker AS "@Sym"
						FOR XML PATH('Instrmt'), TYPE),
					(	SELECT	@Quantity AS "@Qty"
						FOR XML PATH('OrdQty'), TYPE)
			FOR XML PATH('Order'))
		+ '</FIXML>'

EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT

EXEC usp_AllyInvest_Auth_Header @HTTPSessionID, @URL, 'POST'

IF @TradingPassword IS NOT NULL
	BEGIN
		EXEC SQLHTTP.net.RequestHeaderSet @HTTPSessionID, 'TKI_TRADEPASS', @TradingPassword
	END

IF @OverrideWarnings = 1
	BEGIN
		EXEC SQLHTTP.net.RequestHeaderSet @HTTPSessionID, 'TKI_OVERRIDE', 'true'
	END

EXEC SQLHTTP.net.HTTPRequest	@HttpSessionID, 
				@URL = @URL,
				@Method = 'POST',
				@Body = @FIXML,
				@StatusCode = @StatusCode OUTPUT,
				@StatusDescription = @StatusDescription OUTPUT,
				@Response = @Response OUTPUT
GO

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

CREATE PROCEDURE [dbo].[usp_AllyInvest_Order_Create](

@Account varchar(20),

@Ticker varchar(20),

@Quantity int,

@TimeInForce varchar(20),

@Type varchar(20),

@Price numeric(10,2),

@SideOfMarket varchar(20),

@SecurityType varchar(20),

@TradingPassword varchar(50) = NULL,

@OverrideWarnings bit = 0,

@Response nvarchar(MAX) OUTPUT,

@StatusCode int OUTPUT,

@StatusDescription nvarchar(MAX) OUTPUT)

DECLARE @TmInForce char(1)

DECLARE @Typ char(1)

DECLARE @Px varchar(15)

DECLARE @StopPx varchar(15)

DECLARE @Side char(1)

DECLARE @AcctTyp char(1)

DECLARE @SecTyp varchar(3)

SET @Typ = CASE @Type

WHEN 'Market' THEN '1'

WHEN 'Limit' THEN '2'

WHEN 'Stop' THEN '3'

WHEN 'Stop Limit' THEN '4'

WHEN 'Trailing Stop' THEN 'P'

END

SET @TmInForce = CASE

WHEN @Typ = '1' THEN '0'

WHEN @TimeInForce = 'Day' THEN '0'

WHEN @TimeInForce = 'GTC' THEN '1'

WHEN @TimeInForce = 'Market' THEN '7'

END

IF @Typ IN ('2', '4') AND @Price > 0

BEGIN

SET @Px = CONVERT(varchar(15), @Price)

END

ELSE

BEGIN

SET @StopPx = CONVERT(varchar(15), @Price)

END

SET @Side = CASE @SideOfMarket

WHEN 'Buy' THEN '1'

WHEN 'Sell' THEN '2'

WHEN 'Short' THEN '5'

WHEN 'Cover' THEN '1'

END

IF @SideOfMarket = 'Cover'

BEGIN

SET @AcctTyp = '5'

END

SET @SecTyp = CASE @SecurityType

WHEN 'Stock' THEN 'CS'

WHEN 'Option' THEN 'OPT'

END

DECLARE @URL nvarchar(MAX)

DECLARE @HTTPSessionID uniqueidentifier

SET @URL = 'https://api.tradeking.com/v1'

EXEC SQLHTTP.net.URLBuilder @URL OUTPUT,

'TradeKing',

'accounts', @Account,

'orders.xml'

DECLARE @FIXML varchar(MAX)

SELECT @FIXML = '<?xml version="1.0" encoding="UTF-8"?>'

+ '<FIXML xmlns="http://www.fixprotocol.org/FIXML-5-0-SP2">'

+ ( SELECT @TmInForce AS "@TmInForce",

@Typ AS "@Typ",

@Px AS "@Px",

@StopPX AS "@StopPx",

@Side AS "@Side",

@AcctTyp AS "@AcctTyp",

@Account AS "@Acct",

( SELECT @SecTyp AS "@SecTyp",

@Ticker AS "@Sym"

FOR XML PATH('Instrmt'), TYPE),

( SELECT @Quantity AS "@Qty"

FOR XML PATH('OrdQty'), TYPE)

FOR XML PATH('Order'))

+ '</FIXML>'

EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT

EXEC usp_AllyInvest_Auth_Header @HTTPSessionID, @URL, 'POST'

IF @TradingPassword IS NOT NULL

BEGIN

EXEC SQLHTTP.net.RequestHeaderSet @HTTPSessionID, 'TKI_TRADEPASS', @TradingPassword

END

IF @OverrideWarnings = 1

BEGIN

EXEC SQLHTTP.net.RequestHeaderSet @HTTPSessionID, 'TKI_OVERRIDE', 'true'

END

EXEC SQLHTTP.net.HTTPRequest @HttpSessionID,

@URL = @URL,

@Method = 'POST',

@Body = @FIXML,

@StatusCode = @StatusCode OUTPUT,

@StatusDescription = @StatusDescription OUTPUT,

@Response = @Response OUTPUT



Sample Usage:


DECLARE @Response nvarchar(MAX)
DECLARE @StatusCode int
DECLARE @StatusDescription nvarchar(MAX)

EXEC usp_AllyInvest_Order_Create
			@Account = 'your-account-number',
			@Ticker = 'GOOG',
			@Quantity = 10,
			@TimeInForce = 'Day',
			@Type = 'Limit',
			@Price = 1000,
			@SideOfMarket = 'Buy',
			@SecurityType = 'Stock',

			@TradingPassword = 'your-account-password',
			@OverrideWarnings = 0,

			@Response = @Response OUTPUT, 
			@StatusCode = @StatusCode OUTPUT, 
			@StatusDescription = @StatusDescription OUTPUT

IF @StatusCode >= 400
	EXEC SQLHTTP.net.RaiseHttpError @StatusCode, @StatusDescription, @Response
ELSE
	BEGIN

		DECLARE @X xml
		SET @X = CONVERT(xml, CONVERT(varchar(MAX), @Response))
		
		--The XPath syntax below was easily generated by executing the following commands:
		--EXEC SQLHTTP.net.XQueryHelper @X
		--and then executing: 
		--EXEC SQLHTTP.net.XQueryHelper @X, 'response' --

		SELECT T.C.value(N'@id', N'nvarchar(MAX)') AS [id]
			,T.C.value(N'elapsedtime[1]', N'nvarchar(MAX)') AS [elapsedtime]
			,T.C.value(N'clientorderid[1]', N'nvarchar(MAX)') AS [clientorderid]
			,T.C.value(N'orderstatus[1]', N'nvarchar(MAX)') AS [orderstatus]
			,T.C.value(N'error[1]', N'nvarchar(MAX)') AS [error]
		FROM @X.nodes(N'/response') T(C)

	END

DECLARE @Response nvarchar(MAX)

DECLARE @StatusCode int

DECLARE @StatusDescription nvarchar(MAX)

EXEC usp_AllyInvest_Order_Create

@Account = 'your-account-number',

@Ticker = 'GOOG',

@Quantity = 10,

@TimeInForce = 'Day',

@Type = 'Limit',

@Price = 1000,

@SideOfMarket = 'Buy',

@SecurityType = 'Stock',

@TradingPassword = 'your-account-password',

@OverrideWarnings = 0,

@Response = @Response OUTPUT,

@StatusCode = @StatusCode OUTPUT,

@StatusDescription = @StatusDescription OUTPUT

IF @StatusCode >= 400

EXEC SQLHTTP.net.RaiseHttpError @StatusCode, @StatusDescription, @Response

ELSE

BEGIN

DECLARE @X xml

SET @X = CONVERT(xml, CONVERT(varchar(MAX), @Response))

--The XPath syntax below was easily generated by executing the following commands:

--EXEC SQLHTTP.net.XQueryHelper @X

--and then executing:

--EXEC SQLHTTP.net.XQueryHelper @X, 'response' --

SELECT T.C.value(N'@id', N'nvarchar(MAX)') AS [id]

,T.C.value(N'elapsedtime[1]', N'nvarchar(MAX)') AS [elapsedtime]

,T.C.value(N'clientorderid[1]', N'nvarchar(MAX)') AS [clientorderid]

,T.C.value(N'orderstatus[1]', N'nvarchar(MAX)') AS [orderstatus]

,T.C.value(N'error[1]', N'nvarchar(MAX)') AS [error]

FROM @X.nodes(N'/response') T(C)

END

id elapsedtime clientorderid orderstatus error

------------------ ------------- --------------- -------------- ----------

da25......5688ace 0 SV.........830 UnderReview Success



IMPORTANT DISCLAIMER

CODE/SQL ON THESE PAGES ARE PROVIDED AS-IS AND ARE AVAILABLE FOR ILLUSTRATIVE PURPOSES ONLY.

USERS ARE REQUIRED TO ABIDE BY THE TERMS AND CONDITIONS FOR USING REFERENCED THIRD PARTY WEBSITES AND/OR APIs FROM THEIR RESPECTIVE WEBSITES. WE DO NOT CONDONE ANY VIOLATION OF THIRD PARTY WEBSITES AND/OR APIs TERMS AND CONDITIONS USING OUR SOFTWARE.

USERS SHALL BE SOLELY RESPONSIBLE AND BE SOLELY LIABLE FOR VIOLATION OF ANY RULES SPECIFIED BY THIRD PARTIES FOR USING THEIR WEBSITES AND/OR APIs, OR INFRINGEMENT OF RIGHTS OF SUCH THIRD PARTIES.