Functions

SQL Functions dedicated to the Exchange Betting

Ipposnif functions are SQL Server functions dedicated to the exchange markets betting.
To call them from a strategy it is needed to specify the schema owner, in our case it is always dbo.
So for example a call to a function could be:

dbo.fn_TickAdd(@best_back_price, 1)
You can modify these SQL functions, or add new ones, from the Ipposnif database.
SQL used by Ipposnif is not case sensitive, so you can write the function names as you wish.

Progressions

Properties

Utilities

Indicators

 


dbo.fn_AlembertSize

Syntax:
dbo.fn_AlembertSize(id_strategy, unit_value, current_price, bid_type)

Output:
(decimal) Returns the size of the next bet in an Alembert progression. If returns zero the strategy has run out of money.
You can get more info from the strategy "Betting Auto - DAlembert Back".

Input parameters:
id_strategy (integer)Id of the strategy.
unit_value (decimal) The amount of money for every unit in the D'Alambert progression.
current_price (decimal)The price of the horse the strategy is going to bet; normally are horse with an odd very close to the even odd. This value is used by the function to adjust the size of the bet.
bid_type (b|l)The type of bets placed by the Alembert progression; if b all the bets will be back type, if l all the bets will be lay type.
 


dbo.fn_BetDateTime

Syntax:
dbo.fn_BetDateTime(id_trade, bet_num, property)

Output:
(datetime) Returns timestamp properties of a bet. It can be used only if the trade already exists.

Input parameters:
id_trade (integer)Id of the trade
bet_num (integer) bet ordinal position into the trade
property (string) Can return one of the following timestamps:
  • 'place' = timestamp when the bet was placed
  • 'match' = timestamp when the bet was matched
 


dbo.fn_BetProperty

Syntax:
dbo.fn_BetProperty(id_trade, bet_num, property)

Output:
(decimal) Returns properties of a bet. It can be used only if the trade already exists.

Input parameters:
id_trade (integer)Id of the trade
bet_num (integer)Index of the bet
property (string) Can return one of the following properties.
  • 'avg_price' = average price of a matched bet. The value is weighted with the amounts. If no part of the bet has been matched the function returns 0.
  • 'matched_size' = total amount of sizes matched
  • 'original_price' = the original requested price of the bet (before eventual updates).
  • 'original_size' = the original requested size of the bet (before eventual updates).
  • 'required_price' = required price of the bet (during an update)
  • 'required_size' = required size of the bet (during an update)
  • 'status' = status of the bet. Can be:
    0 = not exists
    1 = new (waiting for BF confirmation)
    2 = umatched (with BF confirmation)
    3 = partially matched
    4 = full matched
    5 = cancelled
    6 = settled
    7 = lapsed
  • 'remaining_size' = size of the bet remained unmatched
  • 'bid_type' = 0 (BACK) 1 (LAY)
 


dbo.fn_CustomProgression

Syntax:
dbo.fn_CustomProgression(id_strategy, stake_values, chase_mode)

Output:
(decimal) Returns the size of the next bet in a custom progression. If returns zero the strategy has run out of money.

Input parameters:
id_strategy (integer)Id of the strategy.
stake_values (string) comma separated list of the bet amounts in the progression. The amounts can have two decimals, and the decimal separator is always a dot (.) whatever are the local settings of your PC. No spaces.
chase_mode (string)Use 'w' for a progression of winning results or 'l' for a progression in loss.
Example:
dbo.fn_CustomProgression(@id_strategy, '1.00,1.50,2.50,3.00', 'w')
 


dbo.fn_Fibonacci

Syntax:
dbo.fn_Fibonacci(n)

Output:
(integer) Returns the size in units of the position n in a Fibonacci progression.
In the Fibonacci progression the position 1 has size 1, the position 2 has size 2, from the third position the size is the sum of the sizes of the two previous positions: 3, 5, 8, 13, 21, etc.

Input parameters:
n (integer)Position in the Fibonacci progression.
Example: In case of a row of winnings the bet size grows up following the Fibonacci progression.
dbo.fn_fibonacci(1+dbo.fn_StrategyProperty(@id_strategy,'winning_trades_in_row'))
 


dbo.fn_GetPrice

Syntax:
dbo.fn_GetPrice(id_race, id_horse, bid_type, pos)

Output:
(decimal) Returns the price at a specified position. When pos=1 returns the best price.

Input parameters:
id_race (integer)Id of the market
id_horse (integer)Id of the runner
bid_type (string)'b'=back 'l'=lay
pos (integer)Position of the price to retrieve
 


dbo.fn_GetStake

Syntax:
dbo.fn_GetStake(id_race, id_horse, bid_type, pos)

Output:
(decimal) Returns the stake at a specified position. When pos=1 returns the stake of the best price.

Input parameters:
id_race (integer)Id of the market
id_horse (integer)Id of the runner
bid_type (string)'b'=back 'l'=lay
pos (integer)Position of the stake to retrieve
 


dbo.fn_Global

Syntax:
dbo.fn_Global(index)

Output:
(decimal) Returns the value inputted in one of the eight global variables that you can edit from the options.

Input parameters:
index (integer)Id of the global variable. It can be a number between 1 and 8.
 


dbo.fn_HorseProperty

Syntax:
dbo.fn_HorseProperty(id_horse, property)

Output:
(decimal) Returns properties of an horse.

Input parameters:
id_horse (integer)Id of the runner
property (string)Can return one of the following properties:
  • 'flag' = if 0 the horse has no flag, if 1 the horse has a red flag, if 2 the horse has a black flag
  • 'birth_year' = the birth year of the horse in the format yyyy
  • 'days_since_last_run' = Number of days elapsed from the last run
  • 'official_rating' = official rating
Example: Returns the number of days elapsed since the last run of the horse.
dbo.fn_horseproperty(@id_horse,'days_since_last_run')
 


dbo.fn_MasanielloSize

Syntax:
dbo.fn_MasanielloSize(id_strategy, capital, win_events, tot_events, price, bid_type, skip_last)

Output:
(decimal) Returns the size of the next bet in a Masaniello progression.
If returns zero the round is ended and the strategy can be reset; this can happen for one of the following reasons:
  • The required number of winning events has been reached.
  • The maximum number of losing events in a round has been reached.
  • The last bet was winning but partially matched. In this case the capital could be not enough to finish the round.
  • The next bet is the last bet of the round and the parameter skip_last is 1.
You can get more info from the strategy Masaniello.

Input parameters:
id_strategy (integer)Id of the strategy.
capital (decimal) Capital used by Masaniello progression. it is a good practice to give to the strategy a capital 10% greater than the one specified in this parameter: this to compensate eventual discrepancies, due for example the retirement of a horse or a dead heat.
win_events (integer)The number of events we guess to win.
tot_events (integer)The total number of events of the progression.
price (decimal)The minimum price at which the bets will be placed.
bid_type (b|l)The type of bets placed by the Masaniello progression; if b all the bets will be back type, if l all the bets will be lay type.
skip_last (0|1)If it is equal to 1, the last bet of the Masaniello round is not placed and the strategy is reset. This is useful in a capital conservative approch, where we don't want to risk most of the capital of the round in a single bet (the last).
Example:
dbo.fn_masaniellosize(@id_strategy, 2, 6, 16, 1.96, 'b', 0)
 


dbo.fn_PercAdd

Syntax:
dbo.fn_PercAdd(price, incr)

Output:
(decimal) Returns the input price increased/decreased by incr percentage.

Input parameters:
price (decimal)The price to increase.
incr (-/+decimal)When positive, it is the percentage of increment, when negative, it is the percentage of decrement.
Example: returns 10.5
dbo.fn_PercAdd(9, 20)
Example: returns 7.40
dbo.fn_PercAdd(9, -20)
 


dbo.fn_PercDiff

Syntax:
dbo.fn_PercDiff(price1, price2)

Output:
(decimal) Returns the difference in percentage between price1 and price2. When price2 is greater than price1 the result is positive, otherwise is negative.

Input parameters:
price1 (decimal)First price of the range.
price2 (decimal)Second price of the range.
Example: returns 21.62
select dbo.fn_PercDiff(7.4, 9)
 


dbo.fn_PriceToStake

Syntax:
dbo.fn_PriceToStake(id_race, id_horse, bidtype, stake)

Output:
(decimal) Returns the price at which the stake will be full matched.

Input parameters:
id_race (integer)Id of the market.
id_horse (integer)Id of the runner.
bidtype (b|l)Type of the bet: b=back l=lay.
stake (decimal)Amount ot bet
Example: returns the price at which a back bet of 100 will bet full matched, in the race and for the horse.
dbo.fn_PriceToStake(@id_race, @id_horse, 'b', 100)
 


dbo.fn_RaceHorseProperty

Syntax:
dbo.fn_RaceHorseProperty(id_race, id_horse, property, index)

Output:
(decimal) Returns properties of an horse in a specific race.

Input parameters:
id_race (integer)Id of the market.
id_horse (integer)Id of the runner.
property (string) Can return one of the following properties:
  • 'back_prices' = back price at the [index] position. When [index]=1 returns the best back price in that moment.
  • 'back_sizes' = back size amount at the [index] position
  • 'lay_prices' = lay price at the [index] position
  • 'lay_sizes' = lay size amount at the [index] position
  • 'starting_price' = the last recorded price before the market closed or went in play.
  • 'tam' = total amount matched for that in horse in that race.
  • 'min_matched_price' = the minimum price recorded for that horse before the race closed or went in play
  • 'max_matched_price' = the maximum price recorded for that horse before the race closed or went in play
  • 'number' = the number of the horse
  • 'draw' = the draw number of the horse
  • 'weight' = the weight of the horse. In default it is expressed in pounds. You can switch to kilograms from the options.
  • 'rec_back_price' = return one historical back price. Prices are available at minute: 1, 2, 3, 4, 5, 10, 15, 30, 60, 120, 180 to the start. Use index to specify the minute.
index (integer)When the property returns an array (for ex. back_prices), with index you specify the ordinal position of the value you want.
If the property returns one single value (for ex. tam), this parameter is unused.
Example: returns the ratio between the total amount matched on a horse and the total amount matched on the race.
dbo.fn_racehorseproperty(@id_race, @id_horse, 'tam', 1)
  /dbo.fn_raceproperty(@id_race, 'tam', 1)
 


dbo.fn_RaceProperty

Syntax:
dbo.fn_RaceProperty(id_race, property, index)

Output:
(decimal) Returns properties of a specific race.

Input parameters:
id_race (integer)Id of the market.
property (string) Can return one of the following properties:
  • 'distance' = return the distance of the race in meters
  • 'in_play' = returns the in-play status of the race: 0 the race didn't go in-play, if 1 the race went in-play
  • 'turn_in_play_enabled' = return the in-play type of the race: if 0 the race will not go in-play, if 1 the race will go in-play
  • 'seconds_to_the_start' = seconds remaining to the scheduled start of the race
  • 'seconds_in_play' = seconds elapsed since the race went in-play
  • 'minutes_to_the_start' = minutes remaining to the scheduled start of the race
  • 'overround_back' = overround back percentage of the race. More it is close to 100 more the back prices are fair
  • 'overround_lay' = overround lay percentage of the race. More it is close to 100 more the lay prices are fair
  • 'runners' = count of the runners of the race. Retired horses are not included
  • 'tam' = total amount matched in a race (all the horses)
  • 'race_delay' = delay in seconds between the scheduled time of start and the timestamp of start. Normally is used passing as [id_race] the variable @id_race_prev
  • 'id_horse_winner' = returns the id of the winning horse. It can be used only on markets of type WIN, passing @id_race_prev as first parameter.
  • 'favourite_by_odd' = returns the id of the horse in the position index. Horses are ordered by last price matched
  • 'favourite_by_odd_desc' = returns the id of the horse in the position index. Horses are ordered by last price matched descending
  • 'favourite_by_bf' = returns the id of the horse in the position index. Horses are ordered by Betfair order index
  • 'favourite_by_bf_desc' = returns the id of the horse in the position index. Horses are ordered by Betfair order index descending
index (integer)When the property returns an array (for ex. favourite_by_odd), with index you specify the ordinal position of the value you want.
If the property returns one single value (for ex. tam), this parameter is unused.
Example: place bet if market is of type WIN and the favourite of the previous race has won.
dbo.fn_RaceProperty (@id_race_prev, 'favourite_by_odd', 1) =
	dbo.fn_RaceProperty (@id_race_prev, 'id_horse_winner', 1)
 


dbo.fn_RunnersCount

Syntax:
dbo.fn_RunnersCount(id_race, lower_price, upper_price)

Output:
(bit) Return the number of runners with the last matched price between the range specified..

Input parameters:
id_race (integer)Id of the market
lower_price (decimal)Lower bound of the price range
upper_price (decimal)Upper bound of the price range
 


dbo.fn_StakeDiff

Syntax:
dbo.fn_StakeDiff(id_race, id_horse, price1, price2)

Output:
(decimal) Returns the sum of pending stakes between price1 and price2, for the race and the horse specified. Unmatched bets placed by Ipposnif are not included.

Input parameters:
id_race (integer)Id of the market.
id_horse (integer)Id of the runner.
price1 (decimal)First price of the range.
price2 (decimal)Second price of the range.
Example:
dbo.fn_StakeDiff(@id_race, @id_horse, 2.4, 2.9)
 


dbo.fn_StrategyId

Syntax:
dbo.fn_StrategyId(strategy_name)

Output:
(integer) Returns the id of the strategy with name equals to the input parameter strategy_name (match case is off). When you want to refer across strategies, you have to use the names and not IDs because IDs are automatically assigned during creation/import of the strategies and can be different on different PCs.

Input parameters:
strategy_name (string)Name of the strategy.
Example: returns 1
dbo.fn_StrategyId('AT BACK FAVOURITE')
 


dbo.fn_StrategyProperty

Syntax:
dbo.fn_StrategyProperty(id_strategy, property)

Output:
(decimal) Returns properties of a specific strategy.

Input parameters:
id_strategy (integer)Id of the strategy. If you need to retrieve a value from within the same strategy simply pass, as parameter, the variable @id_strategy. If you want to retrieve a value from another strategy use the function dbo.fn_StrategyId()
property (string) Can return one of the following properties:
  • 'initial_capital' = the initial capital of the strategy
  • 'profit' = current net profit or net loss of the strategy (BF fees are deducted)
  • 'capital' = current capital of the strategy (initial_capital+profit)
  • 'liability' = current total exposure of the strategy
  • 'balance' = capital of the strategy available for betting (capital-liability)
  • 'requested_trades' = count of requested trades of the strategy
  • 'settled_trades' = count of settled trades of the strategy
  • 'winning_trades' = count of winning trades of the strategy
  • 'today_trades' = count of today settled trades of the strategy
  • 'today_winning_trades' = count of today winning settled trades of the strategy
  • 'today_profit' = today net profit or net loss of the strategy (BF fees are deducted)
  • 'winning_trades_row' = count of current winning trades in a row of the strategy
  • 'losing_trades_row' = count of current losing trades in a row of the strategy
  • 'winning_reset_row' = count of current winning reset in a row of the strategy
  • 'losing_reset_row' = count of current losing reset in a row of the strategy
  • 'historical_net_profit' = Returns the net profit and loss for all the rounds (resets) performed by the strategy
  • 'min_since_last_trade' = minutes elapsed since last trade of the strategy
  • 'last_bet_id_horse' = Id of the runner played in the last matched bet, not yet settled, of the strategy. This is useful when from a strategy we want to bet the same runner played by another strategy.
  • 'last_bet_id_race' = Id of the market played in the last matched bet, not yet settled, of the strategy. This is useful when from a strategy we want to know the price at which a bet has been placed by another strategy.
  • 'last_bet_price' = price of the last matched bet, not yet settled, of the strategy.
  • 'is_last_bet_partially_matched' = returns 1 if the last settled bet was partially matched
  • 'last_bet_profit' = returns the net gross profit (including Betfair fees) of the last settled bet
  • 'id_horse_last_trade' (DEPRECATED)
  • 'id_race_last_trade' (DEPRECATED)
Example: place bet if the strategy with name 'MyRefStrategy' has a today profit&loss greater than 20
dbo.fn_strategyproperty(dbo.fn_StrategyId('MyRefStrategy'),'today_profit') > 20
 


dbo.fn_TickAdd

Syntax:
dbo.fn_TickAdd(price, incr)

Output:
(decimal) Returns the input price increased/decreased by a number of ticks equals to [incr] parameter.

Input parameters:
price (decimal)The price to increase.
incr (-/+decimal)When positive is the number of ticks added, when negative is the number of ticks subtracted.
Example: returns 10.5
dbo.fn_TickAdd(10, 1)
Example: returns 9.8
dbo.fn_TickAdd(10, -1)
 


dbo.fn_TickDiff

Syntax:
dbo.fn_TickDiff(price1, price2)

Output:
(decimal) Returns the difference in ticks between price1 and price2. When price2 is greater than price1 the result is positive, otherwise is negative.

Input parameters:
price1 (decimal)First price of the range.
price2 (decimal)Second price of the range.
Example: returns 8
dbo.fn_TickDiff(7.4, 9)
Example: returns -1
dbo.fn_TickDiff(10, 9.8)
 


dbo.fn_TradeCount

Syntax:
dbo.fn_TradeCount(id_race, id_horse, id_strategy, status, pl)

Output:
(decimal) Returns the count of trades for a specific horse, race, strategy, status, p&l.

Input parameters:
id_race (integer)Id of the market. If zero, all trades from all races will be included.
id_horse (integer)Id of the runner. If zero, all trades from all horses will be included.
id_strategy (integer)Id of the strategy. If zero, all trades from all strategies will be included.
status (string)String containing one or more status codes.
Insert an empty string to count trades in all statuses.
Status codes of trades can be:
  • 'O' = open
  • 'C' = cancelled
  • 'F' = closed
  • 'S' = settled
pl (integer)Profit and loss status of the trades. Can be:
  • 0 = all
  • 1 = trades with positive profit
  • 2 = trades with negative profit
Example: returns the count of trades, in an open or closed status, for the current strategy, for all races and for all horses.
dbo.fn_TradeCount(0,0,@id_strategy,'FO',0)
 


dbo.fn_TradeProperty

Syntax:
dbo.fn_WOM(id_race, id_horse)

Output:
(decimal) Returns the Weight Of Money for a specific market-runner combination.

WOM is calculated with the following formula (where Bx and Lx are the available stake amounts for the best 5 back and lay prices): (B1 + B2*0.5 + B3*0.25 + B4*0.12 + B5*0.06) / (B1 + B2*0.5 + B3*0.25 + B4*0.12 + B5*0.06 + (B1 + B2*0.5 + B3*0.25 + B4*0.12 + B5*0.06 + L1 + L2*0.5 + L3*0.25 + L4*0.12 + L5*0.06)

Input parameters:
id_race (integer)Id of the market
id_horse (integer)Id of the runner
 


dbo.fn_WOM

Syntax:
dbo.fn_WOM(id_race, id_horse)

Output:
(decimal) Returns the Weight Of Money for a specific market-runner combination.

WOM is calculated with the following formula (where Bx and Lx are the available stake amounts for the best 5 back and lay prices): (B1 + B2*0.5 + B3*0.25 + B4*0.12 + B5*0.06) / (B1 + B2*0.5 + B3*0.25 + B4*0.12 + B5*0.06 + (B1 + B2*0.5 + B3*0.25 + B4*0.12 + B5*0.06 + L1 + L2*0.5 + L3*0.25 + L4*0.12 + L5*0.06)

Input parameters:
id_race (integer)Id of the market
id_horse (integer)Id of the runner
 


dbo.fn_YankeeSize

Syntax:
dbo.fn_YankeeSize(id_strategy, events, unit_stake)

Output:
(decimal) Returns the size of next bet in a Yankee progression.

The progression can be of 3, 4 or 5 events. A Yankee*3 events produces 3 doubles and 1 trebles for a total of 4 combinations. In this case if the unit_stake is 1, the capital required for a round of the Yankee is 4*1=4.
A Yankee*4 produces: 6 doubles + 4 trebles + 1 fold4 = 11 combinations.
A Yankee*5 produces: 10 doubles + 10 trebles + 5 fold4 + 1 fold5 = 26 combinations.

Input parameters:
id_strategy (integer)Id of the strategy
events (integer)Total number of the events. It can be a value between 3 and 5.
unit_stake (decimal)Amount of money invested in each combination of the yankee.
Example: returns the next bet size in a Yankee*4, using 1/11 of the initial capital of the strategy as unit stake.
dbo.fn_YankeeSize (@id_strategy, 4, @initial_capital/11)