Variables

SQL Variables dedicated to the Exchange Betting

Ipposnif variables contain information about the markets, the runners and more. They can be used in SQL Expressions and SQL Assertions of the strategies.

Some variables can be used directly, for example @capital contains the current capital of the strategy (initial capital+profit), and can be used, for example, to specify the size of the next bet:
@capital/100
Instead, Index Variables are tipically used as parameters passed to functions.
dbo.fn_HorseProperty(@id_horse, 'official_rating') > 99
Every time Ipposnif checks a SQL condition it provides the index variables of the strategy, market, and runner checked at that moment. The image below is a visual explanation of this concept:
Main index variables

All the Ipposnif variables are read mode only: they can be read from a strategy, but they cannot be overwritten.
If you want to show the value of a variable or SQL expression, open Expression Watcher (righ-click on a horse name) and add the variable to the list of the expressions.
If the variable you need is missing, read in the Customize page how to add a new one.

Index Variables

Market Variables

Runner Variables

Trade Variables

User Variables

Strategy Variables

 


@active_markets

Type: Integer
Description: Count of active markets for the current strategy. A market is considered active when it has bets not yet settled. It includes also the market where the strategy is going to place the next bet, so the value returned is always >= 1
Example:
CASE WHEN @active_markets < 3 THEN 10 ELSE 5 END
 


@balance

Type: Decimal
Description: Betfair balance of the user (money available for betting). This value is taken directly from Betfair.
Example: If the balance falls below 100 stop placing bets (a size of zero means no bet), otherwise use a size of 5 for the next bet.
case when @balance < 100 then 0 else 5 end
 


@base_rate

Type: Decimal
Description: Betfair market base rate percentage. You can read the Base Rate of a market from the label close to the Hedge Button in the main screen.
Example: Place bets only on markets that have a Base Rate lower than 7%.
@base_rate < 7
 


@back_forecast_units

Type: Integer
Description: Number of stake units to bet on the BACK forecast. This variable is used in the integration with external forecast system via Excel file.
Example:
0.5 * @back_forecast_units
 


@best_back_price

Type: Decimal
Description: Best BACK price of the runner processed at that moment. To get the BACK prices from other positions use the function dbo.fn_GetPrice.
Example: Place bets only on runners with a best BACK price between 2.2 and 3.2
@best_back_price between 2.2 and 3.2
 


@best_lay_price

Type: Decimal
Description: Best LAY price of the runner processed at that moment. To get the LAY prices from other positions use the function dbo.fn_GetPrice.
Example: Place bets only on runners with a best LAY price between 2.2 and 3.2
@best_lay_price between 2.2 and 3.2
 


@bf_market_id

Type: String
Description: Betfair market ID. It can be useful to integrate Ipposnif with external services.
 


@bf_order_index

Type: Integer
Description: Betfair ordinal position of the runner.
Example: Place bets only on runners at the first position in the Betfair order.
@bf_order_index=1
 


@bf_selection_id

Type: String
Description: Betfair selection ID. It can be useful to integrate Ipposnif with external services.
 


@bfliability

Type: Decimal
Description: Betfair liability of the user (total risked money). It is the sum of the liability of all the Ipposnif strategies, plus the bets placed outside Ipposnif. This value is taken directly from Betfair.
Example: Place bets only if the current liability is lower than 100.
@bfliability < 100
 


@bid_type

Type: String
Description: Bid type (b=BACK l=LAY) entered by the user in the configuration panel of the strategy.
Example: Returns the size of the next bet in a Masaniello progression, expecting 5 wins out of 10 events, with a reference price of 4, and a capital equal to the global variable n.1.
dbo.fn_masaniellosize(@id_strategy, @global_01, 4, 10, 5, @bid_type, 0)
 


@capital

Type: Decimal
Description: Current capital of the strategy (initial capital+profit).
Example: Returns a value equal to 1% of the strategy current capital.
@capital/100
 


@competition

Type: String
Description: Name of the competition. It is a column of the events list in the main window of the program.
Example: Place bets only in the Scottish Championship.
@competition = 'Scottish Championship'
 


@country_code

Type: String
Description: Two chars ISO code of the country of the event. Read the full list of country codes
Example: Place bets only USA or South Africa events.
@country_code in ('us','za')
 


@days_since_last_run

Type: Integer
Description: Days elapsed from the last race of the runner.
 


@discounted_base_rate

Type: Decimal
Description: Betfair market base rate deducted of the player percentage discount.
 


@draw_price

Type: Decimal
Description: Price of the horse at which a bet with size @draw_size will hedge the current trade.
 


@draw_size

Type: Decimal
Description: It is used normally with bet of bid type * that means if the size is positive create a bet of type back, if the size is negative create a bet of type lay.
 


@edge_price

Type: Decimal
Description: Best price of the horse queried in that moment: back price if the bet will be of type Back, best lay price if the bet will be of type Lay.
It is used normally with bet of bid type * that means if the size of the bet is positive create a bet of type back, if the size is negative create a bet of type lay.
 


@edge_size

Type: Integer
Description: Size of the bet that edges the current trade.
It is used normally with bet of bid type * that means if the size is positive create a bet of type back, if the size is negative create a bet of type lay.
 


@entry_bet_matched_size

Type: Decimal
Description: Matched size of the bet with index 1 in the current trade.
 


@event_name

Type: String
Description: Contains the name of the event.
Example: Place bets only on markets related to the event 'chelsea v wolves'.
@event_name = 'chelsea v wolves'
 


@event_type

Type: String
Description: Type of the event, basically it is the name of the sport. This variable is useful when Ipposnif is configured to download events of two or more sports.
The possible event types are: Soccer, Tennis, Golf, Cricket, Rugby Union, Rugby League, Boxing, Horse Racing, Motor Sport, E-Sports, Bowls, Special Bets, Volleyball, Cycling, Chess, Athletics, Snooker, Financial Bets, American Football, Baseball, Winter Sports, Basketball, Ice Hockey, Australian Rules, Handball, Darts, Mixed Martial Arts, Greyhound Racing, Politics, Pool.
Example: Place bets only on greyhound races.
@event_type = 'Greyhound Racing'
 


@fees

Type: Decimal
Description: Factor that multiplied for a win amount gives the amount of fees paid to Betfair from this eventual win on the current race. It includes the Market Base Rate and the user Discount Rate. For example in case of a winning bet of 100, if @fees is equal to 0.048, the fees paid to Betfair will be 4.8.
Example: Can be used to include into the size of a bet the fees paid to Betfair.
2*(1+@fees)
 


@flag

Type: Integer
Description: Color of the flag attached to the runner: 0=none flag 1=red flag 2=black flag.
Example: Place a bet on all the runner with an attached red flag.
@flag=1
 


@form

Type: String
Description: Form of the horse.
Example: Place a bet on every horse that has won the last two races.
@form like '%11'
 


@global_0[1-8]

Type: Decimal
Description: Eight global values that can be entered in the options. These values are shared between all the strategies and can be useful, for example, to set a multiplier for the size of the bets.
Example:
@global_01
 


@historical_net_profit

Type: Decimal
Description: Net profit and loss for all the rounds (resets) performed by the strategy.
 


@horse_name

Type: String
Description: Name of the runner. Depending by the event type it can be the name of a horse, the name of a footbal team, a Tennis player and so on.
Example: Place a bet on every runner with a name that starts with an i (SQL used by Ipposnif is not case sensitive).
@horse_name like 'i%'
 


@horse_note

Type: String
Description: Runner notes inserted by the user.
Example: Place a bet on every runner with a note containing the word lead.
@horse_note like '%lead%'
 


@id_favourite_[1-6]

Type: Integer
Description: Id of the three runners with the lowest odd in the race.
Example: Place bets only on the first favourite.
@id_horse=@id_favourite_1
 


@id_horse

Type: Integer
Description: Id of the runner processed at that moment.
Example: Place bets only on the first favourite.
id_horse=@id_favourite_1
 


@id_race

Type: Integer
Description: Id of the market processed at that moment.
Example: Returns the count of trades, in an open or closed status, for the current strategy, for all horses, in the current race.
dbo.fn_TradeCount(@id_race, 0, @id_strategy, 'FO', 0)
 


@id_race_next

Type: Integer
Description: ID of the next race in the same meeting for the same MarketType. If there is no next race, zero is returned.
 


@id_race_prev

Type: Integer
Description: ID of the previous market in the same meeting for the same MarketType. If there is no previous race, zero is returned.
Example: This assertion can be used to place a bet only when 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)
 


@id_trade

Type: Integer
Description: ID of the trade processed at that moment. It is available only after the first bet of the trade is place.
Example: Returns the profit of the current trade.
dbo.fn_TradeProperty(@id_trade, 'profit')
 


@id_strategy

Type: Integer
Description: ID of the strategy processed at that moment.
Example: Returns the actual exposure of the current strategy.
dbo.fn_StrategyProperty(@id_strategy, 'liability')
 


@initial_capital

Type: Decimal
Description: Starting capital of the strategy.
Example: Returns the actual exposure of the current strategy.
dbo.fn_StrategyProperty(@id_strategy, 'liability')
 


@input_price

Type: Decimal
Description: Returns the value inserted by the user in the column "input" of the runners grid.
To show this input column, you have to select the option "Show Price Input Frame" in the "Manual Input" section of the strategy. If in any of the SQL property of the strategy there is a reference to this variable, the column "input" is shown in the runners grid.
 


@is_anyhorse_selected

Type: Bit
Description: If contains 0 no horse is checked in the race queried in that moment, if contains 1 at least one horse is checked.
Example: Places a bet, if at least one horse is selected in the race.
is_anyhorse_selected=1
 


@is_back_forecast

Type: Bit
Description: If contains 1, the horse is in the daily BACK forecasts list. This variable is used in the integration with external forecast system via Excel file.
Example:
@is_back_forecast=1
 


@is_horse_selected

Type: Bit
Description: If contains 0, the horse is not checked, if contains 1 it is checked.
Example: Place bets only on selected runners.
@is_horse_selected=1
 


@is_event_selected

Type: Bit
Description: User can select events in the left frame of the main window, showing the column "Selected" (right click on the columns header). If this variable contains 0, the event has not been selected by the user, if it contains 1, the event is selected. It can be used to trig, with one single event selection, multiple strategies working on different market types.
Example: Place bets only on events selected by the user.
@is_event_selected=1
 


@is_in_play

Type: Bit
Description: If contains 0 the race is not in play, if 1 the race is in play.
Example: Place bets only when the market is not in play.
@is_in_play=0
 


@is_lay_forecast

Type: Bit
Description: If contains 1, the horse is in the daily LAY forecasts list. This variable is used in the integration with external forecast system via Excel file.
Example:
@is_lay_forecast=1
 


@is_market_selected

Type: Bit
Description: Returns 1 if the market has been manually selected by the user. Otherwise it returns zero.
To show this the market checkbox (top-right of the main screen) you have to select the option "Market CHeckbox" in the "Manual Input" section of the strategy.
 


@jockey_name

Type: String
Description: Contains the jockey name of the horse.
Example: Place bets only on horses ridden by Andrea Atzeni.
@jockey_name = 'Andrea Atzeni'
 


@last_check_bet_seconds

Type: Integer
Description: Returns the number of seconds elapsed from the last status change of the previous bet of the same strategy. If there isn't a previous bet in the strategy, -1 is returned. This variable can be useful to prevent a new bet placement for a certain number of seconds from the last bet settlement, allowing an eventual reset condition to be processed before. Reset conditions are checked asyncronously every 15 seconds. Example: Place the bet only if it is the first one or more than 30 seconds are elapsed since the last bet settlement in the same strategy.
@last_check_bet_seconds =-1 OR @last_check_bet_seconds >30
 


@last_reset_historical_profit

Type: Decimal
Description: Returns the historical net profit of the strategy at the moment of the last reset. If there was no previous reset, zero is returned. Example: The initial capital is 1 unit plus 50% of the eventual historical profit of the strategy.
1 + case when @last_reset_historical_profit <  then 0 else @last_reset_historical_profit/2 end
 


@last_unsettled_trade_profit

Type: Decimal
Description: Returns the net hedge profit of the last open (unsettled) trade.
 


@lay_forecast_units

Type: Integer
Description: Number of stake units to bet on the LAY forecast. This variable is used in the integration with external forecast system via Excel file.
Example:
0.5 * @lay_forecast_units
 


@lp_place

Type: Decimal
Description: Returns the last price of the current horse in the market type 'PLACE'. This variable is used normally to get the PLACE price from other market types (for example from the WIN market type).
This variable works only with Horse Racing and greyhound Racing event types.
 


@lp_win

Type: Decimal
Description: Returns the last price of the current horse in the market type 'WIN'. This variable is used normally to get the WIN price from other market types (for example from the PLACE market type).
This variable works only with Horse Racing and greyhound Racing event types.
 


@market_losing_trades

Type: Integer
Description: Count of trades with a negative P&L in the market for the same strategy.
 


@market_matched_trades

Type: Integer
Description: Count of trades with status Finished in the market for the same strategy.
 


@market_name

Type: String
Description: Contains the name of the market. The content can vary from sport to sport, from market type to market type.
For example in horse racing WIN markets it contains the distance of the race and other info.
It is a column of the main window of the program, left frame.
Example: Place bets only races with a distance of six furlongs.
@market_name like '%6f%'
 


@market_profit

Type: Decimal
Description: Net profit of the market for the same strategy.
 


@market_tot_trades

Type: Integer
Description: Count of trades in the market for the same strategy.
 


@market_type

Type: String
Description: Type of the market. An event (for ex. a horse race) can have multiple markets each one of different type (for ex. WIN, PLACE, REV_FORECAST).
Example: Place bets only on WIN type markets.
@market_type = 'WIN'
 


@market_winning_trades

Type: Integer
Description: Count of trades with a positive P&L in the market for the same strategy.
 


@matched_size

Type: Decimal
Description: Total amount matched of the bet where this variable is used.
 


@max_matched_price

Type: Decimal
Description: Maximum price matched for the runner before the race went in play.
 


@min_matched_price

Type: Decimal
Description: Minimum price matched for the runner before the race went in play.
 


@nbet

Type: Integer
Description: Ordinal position, in the trade, of the bet checked at that moment.
Example: Returns the second elpased since the placement of the current bet of the current trade.
dbo.datediff(second, dbo.fn_BetDateTime(@id_trade, @nbet, 'place'), getdate())
 


@number

Type: Integer
Description: Runner number.
Example: Place bets only on runner with number 1.
@number=1
 


@number_of_winners

Type: Integer
Description: Number of winner of the current market. Most of the markets have only one winner, but there are cases (like horse racing Place markets) in which there are more than one.
 


@original_price

Type: Decimal
Description: Returns the original price of the current bet (before it is eventually updated by the strategy).
 


@original_size

Type: Decimal
Description: Returns the original size of the current bet (before it is eventually updated by the strategy)
 


@overround_back

Type: Decimal
Description: Contains the overround back percentage of the market. More it is close to 100 more the back prices are fair.
 


@overround_lay

Type: Decimal
Description: Contains the overround lay percentage of the market. More it is close to 100 more the back prices are fair.
 


@owner_name

Type: String
Description: Contains the owner name of the horse.
 


@position_[min]

Type: Integer
Description: Return the position in the list of runners ordered by price, at fixed intervals at the start of the event. 1 means that the horse was the one with the lowest odd (favourite), 2 means second favourite, etc.. The available intervals [min] are 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 15, 30, 60, 120, 180.
If the position is not yet present, the variable returns null.
 


@price_[min]

Type: Decimal
Description: Return the price at fixed intervals at the start of the event. The available intervals [min] are 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 15, 30, 60, 120, 180.
If the price is not yet present, the variable returns null.
Example: Returns the difference in tips between the current best back price and the price at two minutes at the start. If the price is not yet present (Null), it is converted to the same value of the best back price, producing a difference of zero ticks.
dbo.fn_PercDiff(@best_back_price, isnull(@price_2, @best_back_price))
 


@profit

Type: Integer
Description: Expected hedge profit and loss of the current horse.
 


@racecourse

Type: String
Description: Contains the code of the racecourse of the race.
Example: Place bets only on Lingfield markets.
@racecourse='Lingfield'
 


@rating

Type: Integer
Description: Contains the rating points of the runner. It is available only for some event types (like for ex. GB Horse Races).
 


@return

Type: Decimal
Description: Expected profit and loss of the strategy in case the horse wins. Only matched bets are included.
 


@runner_tam

Type: Decimal
Description: Total amount matched on the runner.
Example: Returns the ratio between the total amount matched on a horse and the total amount matched in the market.
@runner_tam/@tam
 


@runners

Type: Integer
Description: Count of the runners of the race. Retired horses are excluded.
 


@seconds_to_the_start

Type: Integer
Description: Number of remaining seconds at the start of the event.
 


@stall_number

Type: Integer
Description: Stall_number number of the runner.
 


@start_price

Type: Decimal
Description: Last matched price before the market went in play.
 


@tam

Type: Decimal
Description: Total amount matched in the market.
Example: Returns the ratio between the total amount matched on a horse and the total amount matched in the market.
@runner_tam/@tam
 


@trade_profit

Type: Decimal
Description: Contains the actual profit&loss of the current trade.
Example: If the loss of the current trade is greater than 2, perform some actions (like placing an hedge bet).
@trade_profit < -2
 


@trainer_name

Type: String
Description: Contains the name of the trainer of the horse.
Example: Place bets only on horses trained by Richard Hannon.
@trainer_name = 'Richard Hannon'
 


@turned_in_play_enabled

Type: Bit
Description: Returns 1 if the market will go in-play, otherwise returns 0..
Example: If the market will go in-play the bet size is 10, otherwise it is 2.
case when @turned_in_play_enabled=1 then 10 else 2 end
 


@weight

Type: Integer
Description: Weight assigned to the horse. In default it is expressed in pounds. You can switch to kilograms from the options.
 


@worst

Type: Decimal
Description: Expected profit and loss of the strategy in case the horse wins. Matched and unmatched bets are included.
 


@yob

Type: Integer
Description: Contains the year of birth of the horse in the format yyyy
Example: Place bets only on two years old horses.
(datepart(year,getutcdate())-@yob)=2