April 18, 2021 at 7:01 pm
I like it, just need to stop it at the right place e.g no of accas but all I wanted to see was how to join the CTE up with my tables. I remember at my last job converting all adjacency tables (ParentID,ID) into CTES for heirarchial code and I remember my techie manager telling me off. He said something like "Don't waste time learning new things, stick to what you know" - OMG he was such a Luddite. If only he had learned a few new tricks he wouldn't have wasted a year on a custom mail server he had made that was broken to bits.
By the way (apologies in advance) I've already got a Betfair API Key (worth £299 I think now) and an AutoBOT for placing bets e.g place accumulators do pretty well so does DUTCHING, automating the runners to go into those type of bets is pretty easy when you have 25 years+ of racing data (Runners, Races, lb claimed, wind surgery, head gear, stats on everything) today I won on the Scottish Grand National, and had the 2nd as a PLACE ONLY Bet. I'm sure if you look at all the questions I've asked on here over the years many probably have to do with betting.
I wrote my 1st Betfair BOT back in 2010, when it was a simple SOAP based API, 2 classes, and a resource XLS file for course abbreviations was all I needed. I made £1,500 testing it (as there was no "test mode" back then, and you had to use real money), with £2 min bets over the 1st year. Many a time I forgot an exit out of some loop and ended up placing 200 bets on one horse before I could stop it etc. In fact most of the code is actually Safety Measures to prevent things like:
And loads else. I specialise in automation, I used to have my own Racing Tips site, £100py or £5pwk to join, that had it's own race-cards, list of the Top Horses (rated by my own system), and over 550+ betting systems created automatically by the "BRAINIAC" (as I call it), that analyses all the data at night, finds niche systems, ensures they have made ROI over 10% in the last 30 & 100 days, and then offer up the tips.
LAY bets, PLACE ONLY, MULTI DAY WIN/PLACE ACCAS, DUTCHING, NAPS etc, automatic emails to members and people wanting "free tips" as a trial, a lovely looking website with areas that showed the next race's top market leaders, silks, price (WIN & PLACE markets), then below the latest results, a countdown time to the next race, loads of affiliates and automatic DB cleaning, re-indexing, management so I didn't have to do jack. I went on holiday to Iceland once and came back to a nice profit and the system hadn't failed once.
Whilst working I was renting out a VPS and was paying £90pm for hosting bandwidth which it needed - no probs whilst working, however Betfair kindly decided to change their API to a JSON based one, which meant a 2 class system in C# became a 25 class system. A lot more complex and it took at least a year to rewrite in my spare time and meant the website that was bringing in money on an automatic basis halted whilst I rewrote my Windows Service to use the API.
However once I had managed to rewrite it, I was just about to throw a lump into it to see how it would work and how much money I could make when....my company decided to outsource all their servers to OVH in France. Whilst the VPS was in the US, the Windows Service to connect to Betfair was running on a local server for quicker response times etc, and I had a little app on my PC that showed me the current balance, liability, money in use and a STOP/START button to control the service on my PC.
As I was grabbing data from all over the place including some horse racing sites I had build for a number of clients, and news that I posted to help with SEO, auto-tagging, using the tags as #hashtags for posting out to X no of Twitter accounts ( I think you can find my old WordPress based plugins I wrote myself due to the ones I found being so useless on an old site > https://www.strictly-software.com/plugins)
As I was basically getting free DB hosting, and piggy backing off a horse racing site our company had made, it meant my DB going off to OVH as well. I wasn't going to write such a complex DB in MySQL and put it on the VPS - I don't even know if MySQL can do Stored Procs with TRANSACTIONS, ROLLBACKS and returning multiple recordsets - didn't use to when I was working. May do now but the API interface we had was either PHP MyAdmin or Navicat, total BS compared to MS Management Console.
Then I got sick, lost my job and couldn't afford to keep the hosting up, as affiliates were killing off access to European countries by the day so that money dried up, plus people with all the AD Blocker tools etc didn't even see them. That was a big chunk of income lost and I got kicked of Bet365 and SkyBet for basically winning... sounds stupid but it's true if you are not a "Losing" customer you are of no value to a normal bookies. So when affiliates send people to those sites to sign up they get a % of how much they LOSE per month - nice!
So since being on the sick I have no server, two laptops, one "Borrowed" from work, and I have managed to install SQL Server Express 2017 on it and I am making a cut down version of my BOT to run from these laptops. Hopefully make enough money to rent another server, but as ££ sick income is not exactly great I make money on Betfair, Trading or Betting, and I basically have to use it to pay for things, I regularly have to turn £1 into a ton per week so I can withdraw £90 and spend it on things I need, then rinse n repeat.
However lately I have been getting into the football, especially EURO ELITE teams e.g Barcelona, Bayern Munich, PSG, Man City, Lazio, Ajax, Porto etc, and I have been doing accas, a 10p 20 fold acca won me £190. So I aim to make money by not staking much, and the idea of creating so many accas for little money e.g £2 @ 10p per acca = 20 accas, if I just get one right, I am in profit, as I stop at around £5-£6 return per acca which is enough to pay off the TOTAL STAKE across all accas and give me some profit. Obviously the more I win the more money I make and as the betting bank grows so will the stake.
I will hopefully use my regex n scraping skills to automate a lot of the params entered into the main stored proc that comes up with match outcomes but I obviously need to make it work manually before I can automate it all.
I am not a gambler, I am someone who makes money and spends it e.g my 50" UHD TV behind me was bought from winning the Grand National on Many Clouds Antepost at 66/1 in 2015 I think, I paid for a holiday in Iceland with a winning treble at the Breeders Cup, last horse Magician won at 8/1 - fav race of all time. Comes from the back on the home turn, wide (as he hates hitting other horses, literally goes backwards if he does), and Ryan Moore got him on the outside and the commentators didn't even notice him steam up the pack and beat he Fugue by a head. Iceland is expensive - never realised how much, and they drink more than Brits, so I was glad I had won enough to tick it off my bucket list.
So I don't want to end up sat in front of a high pay out slot machine like so many people do in betting shops, in fact I have never entered one since I won £140 on a treble, stopped in a car park that used to be free came back in 5 mins to find I was clamped as they had just changed it to charge people. I had to pay £125 out of that money to get unclamped so never again will I use a shop to bet. I just want to make more angles for making more money. I already won a 55.10 8 fold acca today on the football along with the racing so I am up for the day and never one to chase loses. Key to making money off betting is never to gamble, always bet if there is value, and never use a bookie that will never provide true odds for a horse, use an EXCHANGE like Betfair/Betdaq etc and TRADE. The football thing is new and hopefully I can get the games out without writing a procedural method to split the group up into smaller groups.
Your SQL has been very helpful, and I am sure many people would find it useful just not for betting, but as I said splitting a group of "things" up into random smaller groups of "things" in a SET BASED manner, I could have wrote a long stored proc to do it all with nested loops to spread the love etc but I wanted to see if there was a SET BASED approach to this problem.
Sorry for the life story.... but that's how it went and now goes....
April 19, 2021 at 4:08 pm
Thank you for your responses Rob. It's an interesting story and your experiences with server code ring a bell which really resonates with me. Ha, more could be said about that. For this particular SQL issue one thing I'm not understanding is what indicates how many matches make up an accumulation? It's always 6 or is there an accumulation "batch indicator" within the FOOTBALL_MATCHES table? Or is the number of matches per combo_rn always supposed to equal the distinct count of all GameID's in the table? The code provided doesn't really "apply" the MatchScore it only sums the 'odds' for each combo_rn (or "acca" now I'm using the lingo) and selects the TOP(n).
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
April 19, 2021 at 5:14 pm
Really the accumulator price (total odds=10p stake x N numbers of match outcome odds to be > total stake of £2 and make a 20%-40% profit hopefully) would be used like this a) it has to pay off the rest of the accumulators stake and give yourself profit, around £6 - £8 for EURO ELITE teams, SO the stake per acca (10p * gameoutome1 * gameoutcome2 .... * gameoutcome12 = MORE THAN TOTAL STAKE OF £2 (£2/20=10p per acca).
So each acca has to make more than the stake of £2 for rest of the accas, and try to make as much profit as possible.
B) You may have put many low scoring match outcomes in an acca like Double Chances e.g Man UTD or DRAW or MAN UTD or LIVERPOOL, odds for these match outcomes would be tiny as your not going on the nose, but the aim of those double chances is where there is really little difference in the TeamScore (orig table). In those circumstances you really don't really know the outcome of the game so you are "hedging your bet" in a way by adding two game outcomes together. It's to save an acca really, heping it have more chance of winning bet but to make any money just from double chances from 10p would be the max of 25 bets in an acca so the outsiders which have low MatchOutcomes shouldn't be as frequent as they are.
As upsets they hardly occur so about 3-25% of accas would contain some kind of upset game with high odds - an outsider to win. When I run the code, LEEDS TO WIN appears in nearly all the accas from top to bottom. They were an outsider shot, bigger odds but less likely to win the game (higher odds and lower TeamScore in my table), so they should have just appeared now and then, however a Cartesian join does everything in one set with the opposite set making tons of outcomes but some accas should not contain as many MatchOutcomes that have low scores a higher chance, lower odds, higher Team/MatchOutccom score.
I don't know if you even want to attempt this logic in one set based solution is up to you. It would be some hardcore code and the type of subject you could write a piece for SQLServerCentral.com about, the difficult logic, when cross joins are useful, and a complex solution (I imagine).
Remember....
TBH I don't know if one CrossJoin should be used as it does as match of every outcome to every other outcome. Maybe multiple Cross Joins code is needed each with a TOP(X - Enough to cover the stake and make a bit of profit), So the GameOutcomes with higher scores are spread around a lot, those with middle outcomes a smaller chunk, and the outsiders or a DRAW would only appear 5-20% of the time in an acca. They should really only appear occasionally but sprinkled across the accas/groups.
I don't know whether putting the whole code in one Cartesian set in one is a wise idea or possible.
And only carry on helping me if you like doing this sort of forum help, and difficult code ...... but stop if it's a mare.
So I don't know whether doing 3 different CROSS JOINS is the answer each targeting those 3 different groups top to bottom. Or putting all of your 132 results into a temp table joined to the main tables and split the accas up randomly from your cross-set select outcome. It's just that there shouldn't be 132 results but (Total Stake £2 x price per acca 10p = 20 accas) and 132/6 (average no of teams in an acca off top of my head) and you return 22 groups/accas.
I hope that doesn't sound too complicated and don't even think of tackling it if you don't have the time or think it's too hard in a SET BASED process. However your help is much appreciated and I am actually learning a bit more about SQL after all this time off. Kept up with coding, making little Windows Form Apps for fun, Proxy Hunter, Checker, Decoder and then a Country list with the test result calling a custom page that returns all the headers REMOTE-HOST etc and X-Forwarded For (about 20) etc so returns whether it is an anonymous proxy or your IP addresses have been providing your original IP string IPV6 and a different IPv4 nearly every visit to a What is my IP site. I have no idea how I can turn off broadcasting my main IPv6 address and just leave the IPv4 DCHP picked one that other sites see.
Thanks anyway.
April 22, 2021 at 2:52 pm
Regarding the specifics of trading scenarios it's just not my cup of tea. If there was a specific SQL question (or answer) without any lingo then maybe. But so to backtrack a little you wrote:
"Betfair kindly decided to change their API to a JSON based one, which meant a 2 class system in C# became a 25 class system. A lot more complex and it took at least a year to rewrite in my spare time and meant the website that was bringing in money on an automatic basis halted whilst I rewrote my Windows Service to use the API."
"I have no server, two laptops, one "Borrowed" from work, and I have managed to install SQL Server Express 2017 on it and I am making a cut down version of my BOT to run from these laptops."
"I will hopefully use my regex n scraping skills to automate a lot of the params entered into the main stored proc that comes up with match outcomes but I obviously need to make it work manually before I can automate it all."
"... don't even think of tackling it if you don't have the time or think it's too hard in a SET BASED process."
Maybe you've overcomplicated the conversion. Depending on what procedural language you're working with, moving from SOAP to JSON could be an opportunity to simplify your system not the opposite. I maintain RESTful JSON-based (largely) API's which integrate with other API's some of which are SOAP. The SQL procedure(s) which make up your basic service is/are already written? Roughly how many procedures, tables, etc.? With what procedural language are you working?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
April 24, 2021 at 10:39 pm
Both API's were written in .NET C#.
The Soap Version was very easy to knock up to integrate with their API. You KeepAlive the BOT and when login in you get the Session ID back store it and keep using it. If it stop working you go get a different session to log in with, all automatically of course.
The JSON version has 2 different JSON calls, JsonRpcClient and a RescriptClient JSON access method, I have a preference set to try first and if that fails it goes the other one.
There are 2 public Interfaces, one for IClient (the JSON method to get and return data) and the IAccount interface which does the same for Account Info.
There are a total of 119 classes in v2, compared to 2 in v1. There are a total of 77 tables, and 123 stored procedures, and 63 Scalar UDFS, and 3 table UDFS. MS SQL 2017, compatibility mode 130 (2016), first version was written in SQL 2005,
Here is the Rescript class to get and return data.
using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Linq;
using System.Text;
using Api_ng_sample_code.TO;
using System.Web.Services.Protocols;
using System.Net;
using System.IO;
using Api_ng_sample_code.Json;
namespace Api_ng_sample_code
{
public class JsonRpcClient : HttpWebClientProtocol, IClient
{
public string EndPoint { get; private set; }
private static readonly IDictionary<string, Type> operationReturnTypeMap = new Dictionary<string, Type>();
public const string APPKEY_HEADER = "X-Application";
public const string SESSION_TOKEN_HEADER = "X-Authentication";
public NameValueCollection CustomHeaders { get; set; }
private static readonly string LIST_EVENT_TYPES_METHOD = "SportsAPING/v1.0/listEventTypes";
private static readonly string LIST_MARKET_CATALOGUE_METHOD = "SportsAPING/v1.0/listMarketCatalogue";
private static readonly string LIST_MARKET_BOOK_METHOD = "SportsAPING/v1.0/listMarketBook";
private static readonly string PLACE_ORDERS_METHOD = "SportsAPING/v1.0/placeOrders";
private static readonly string CANCEL_ORDERS_METHOD = "SportsAPING/v1.0/cancelOrders";
private static readonly string CURRENT_ORDER_SUMMARY_METHOD = "SportsAPING/v1.0/listCurrentOrders";
private static readonly string CLEARED_ORDER_SUMMARY_METHOD = "SportsAPING/v1.0/listClearedOrders";
private static readonly string ACCOUNT_FUNDS_METHOD = "AccountAPING/v1.0/getAccountFunds"; //
private static readonly string ACCOUNT_DETAILS_METHOD = "AccountAPING/v1.0/getAccountDetails";
private static readonly String FILTER = "filter";
private static readonly String LOCALE = "locale";
private static readonly String CURRENCY_CODE = "currencyCode";
private static readonly String MARKET_PROJECTION = "marketProjection";
private static readonly String MATCH_PROJECTION = "matchProjection";
private static readonly String ORDER_PROJECTION = "orderProjection";
private static readonly String PRICE_PROJECTION = "priceProjection";
private static readonly String SORT = "sort";
private static readonly String MAX_RESULTS = "maxResults";
private static readonly String MARKET_IDS = "marketIds";
private static readonly String BET_IDS = "betIds";
private static readonly String MARKET_ID = "marketId";
private static readonly String PLACED_DATE_RANGE = "placedDateRange";
private static readonly String INSTRUCTIONS = "instructions";
private static readonly String CUSTOMER_REFERENCE = "customerRef";
private static readonly String AVAILABLE_TO_BET_BALANCE = "availableToBetBalance";
private static readonly String EXPOSURE = "exposure";
private static readonly String RETAINED_COMMISSION = "retainedCommission";
private static readonly String EXPOSURE_LIMIT = "exposureLimit";
private static readonly String WALLET = "wallet";
private static readonly String ORDER_BY = "orderBy";
private static readonly String SORT_DIR = "sortDir";
private static readonly String FROM_RECORD = "fromRecord";
private static readonly String RECORD_COUNT = "recordCount";
private static readonly String BET_STATUS = "betStatus";
private static readonly String EVENT_TYPE_IDS = "eventTypeIds";
private static readonly String EVENT_IDS = "eventIds";
private static readonly String RUNNER_IDS = "runnerIds";
private static readonly String SIDE = "side";
private static readonly String SETTLED_DATE_RANGE = "settledDateRange";
private static readonly String INCLUDE_ITEM_DESCRIPTION = "includeItemDescription";
private static readonly String GROUP_BY = "groupBy";
private HelperLib HelperLib;
public JsonRpcClient(string endPoint, string appKey, string sessionToken)
{
if (this.HelperLib == null)
{
this.HelperLib = new HelperLib();
}
this.EndPoint = endPoint + "/json-rpc/v1/";
CustomHeaders = new NameValueCollection();
if (appKey != null)
{
CustomHeaders[APPKEY_HEADER] = appKey;
}
if (sessionToken != null)
{
CustomHeaders[SESSION_TOKEN_HEADER] = sessionToken;
}
}
public AccountFunds getAccountFunds(Wallet walletName)
{
var args = new Dictionary<string, object>();
args[WALLET] = walletName;
return Invoke<AccountFunds>(ACCOUNT_FUNDS_METHOD, args);
}
public AccountDetails getAccountDetails()
{
var args = new Dictionary<string, object>();
return Invoke<AccountDetails>(ACCOUNT_DETAILS_METHOD, args);
}
public IList<EventTypeResult> listEventTypes(MarketFilter marketFilter, string locale = null)
{
var args = new Dictionary<string, object>();
args[FILTER] = marketFilter;
args[LOCALE] = locale;
return Invoke<List<EventTypeResult>>(LIST_EVENT_TYPES_METHOD, args);
}
public IList<MarketCatalogue> listMarketCatalogue(MarketFilter marketFilter, ISet<MarketProjection> marketProjections, MarketSort marketSort, string maxResult = "1", string locale = null)
{
var args = new Dictionary<string, object>();
args[FILTER] = marketFilter;
args[MARKET_PROJECTION] = marketProjections;
args[SORT] = marketSort;
args[MAX_RESULTS] = maxResult;
args[LOCALE] = locale;
return Invoke<List<MarketCatalogue>>(LIST_MARKET_CATALOGUE_METHOD, args);
}
public IList<MarketBook> listMarketBook(IList<string> marketIds, PriceProjection priceProjection, OrderProjection? orderProjection = null, MatchProjection? matchProjection = null, string currencyCode = null, string locale = null)
{
var args = new Dictionary<string, object>();
args[MARKET_IDS]= marketIds;
args[PRICE_PROJECTION] = priceProjection;
args[ORDER_PROJECTION] = orderProjection;
args[MATCH_PROJECTION] = matchProjection;
args[LOCALE] = locale;
args[CURRENCY_CODE] = currencyCode;
return Invoke<List<MarketBook>>(LIST_MARKET_BOOK_METHOD, args);
}
public CurrentOrderSummaryReport listCurrentOrders(ISet<String> betIds, ISet<String> marketIds, OrderProjection orderProjection, TimeRange placedDateRange, OrderBy orderBy, SortDir sortDir, int fromRecord, int recordCount)
{
var args = new Dictionary<string, object>();
args[BET_IDS] = betIds;
args[MARKET_IDS] = marketIds;
args[ORDER_PROJECTION] = orderProjection;
args[PLACED_DATE_RANGE] = placedDateRange;
args[ORDER_BY] = orderBy;
args[SORT_DIR] = sortDir;
args[FROM_RECORD] = fromRecord;
args[RECORD_COUNT] = recordCount;
return Invoke<CurrentOrderSummaryReport>(CURRENT_ORDER_SUMMARY_METHOD, args);
}
public ClearedOrderSummaryReport listClearedOrders(BetStatus betStatus, ISet<String> eventTypeIds, ISet<String> eventIds, ISet<String> marketIds, ISet<RunnerId> runnerIds, ISet<String> betIds, Side side, TimeRange settledDateRange, GroupBy groupBy, bool includeItemDescription, string locale = null, int fromRecord = 0, int recordCount = 0)
{
var args = new Dictionary<string, object>();
args[BET_STATUS] = betStatus;
args[EVENT_TYPE_IDS] = eventTypeIds;
args[EVENT_IDS] = eventIds;
args[MARKET_IDS] = marketIds;
args[RUNNER_IDS] = runnerIds;
args[BET_IDS] = betIds;
args[SIDE] = side;
args[SETTLED_DATE_RANGE] = settledDateRange;
args[GROUP_BY] = groupBy;
args[INCLUDE_ITEM_DESCRIPTION] = includeItemDescription;
args[LOCALE] = locale;
args[FROM_RECORD] = fromRecord;
args[RECORD_COUNT] = recordCount;
return Invoke<ClearedOrderSummaryReport>(CLEARED_ORDER_SUMMARY_METHOD, args);
}
public ClearedOrderSummaryReport listClearedOrders(BetStatus betStatus, ISet<String> betIds, GroupBy groupBy, bool includeItemDescription, string locale = null, int fromRecord = 0, int recordCount = 0)
{
var args = new Dictionary<string, object>();
args[BET_STATUS] = betStatus;
args[BET_IDS] = betIds;
args[GROUP_BY] = groupBy;
args[INCLUDE_ITEM_DESCRIPTION] = includeItemDescription;
args[LOCALE] = locale;
args[FROM_RECORD] = fromRecord;
args[RECORD_COUNT] = recordCount;
return Invoke<ClearedOrderSummaryReport>(CLEARED_ORDER_SUMMARY_METHOD, args);
}
public ClearedOrderSummaryReport listClearedOrders(BetStatus betStatus, ISet<String> betIds, GroupBy groupBy, ISet<String> marketIds, bool includeItemDescription, string locale = null, int fromRecord = 0, int recordCount = 1)
{
var args = new Dictionary<string, object>();
args[BET_STATUS] = betStatus;
args[BET_IDS] = betIds;
args[GROUP_BY] = groupBy;
args[MARKET_IDS] = marketIds;
args[INCLUDE_ITEM_DESCRIPTION] = includeItemDescription;
args[LOCALE] = locale;
args[FROM_RECORD] = fromRecord;
args[RECORD_COUNT] = recordCount;
return Invoke<ClearedOrderSummaryReport>(CLEARED_ORDER_SUMMARY_METHOD, args);
}
public ClearedOrderSummaryReport listClearedOrders(BetStatus betStatus, ISet<String> marketIds, GroupBy groupBy, Side side, bool includeItemDescription, string locale = null, int fromRecord = 0)
{
var args = new Dictionary<string, object>();
args[BET_STATUS] = betStatus;
args[MARKET_IDS] = marketIds;
args[GROUP_BY] = groupBy;
args[INCLUDE_ITEM_DESCRIPTION] = includeItemDescription;
args[LOCALE] = locale;
args[FROM_RECORD] = fromRecord;
args[RECORD_COUNT] = 100;
args[SIDE] = side;
return Invoke<ClearedOrderSummaryReport>(CLEARED_ORDER_SUMMARY_METHOD, args);
}
public PlaceExecutionReport placeOrders(string marketId, string customerRef, IList<PlaceInstruction> placeInstructions, string locale = null)
{
var args = new Dictionary<string, object>();
args[MARKET_ID] = marketId;
args[INSTRUCTIONS] = placeInstructions;
args[CUSTOMER_REFERENCE] = customerRef;
return Invoke<PlaceExecutionReport>(PLACE_ORDERS_METHOD, args);
}
public CancelExecutionReport cancelOrders(string marketId, IList<CancelInstruction> cancelInstructions, string customerRef)
{
var args = new Dictionary<string, object>();
args[MARKET_ID] = marketId;
args[INSTRUCTIONS] = cancelInstructions;
args[CUSTOMER_REFERENCE] = customerRef;
return Invoke<CancelExecutionReport>(CANCEL_ORDERS_METHOD, args);
}
protected WebRequest CreateWebRequest(Uri uri)
{
HttpWebRequest request = WebRequest.Create(new Uri(EndPoint)) as HttpWebRequest;
request.Method = "POST";
request.ContentType = "application/json-rpc";
request.KeepAlive = true;
request.AutomaticDecompression = DecompressionMethods.Deflate | DecompressionMethods.GZip;
request.Headers.Add("Accept-Encoding", "gzip,deflate");
request.Headers.Add(HttpRequestHeader.AcceptCharset, "ISO-8859-1,utf-8");
request.Headers.Add(CustomHeaders);
return request;
}
public T Invoke<T>(string method, IDictionary<string, object> args = null)
{
if (method == null)
{
new ArgumentNullException("method");
}
if (method.Length == 0)
{
throw new ArgumentException(null, "method");
}
var uri = new Uri(EndPoint);
var request = CreateWebRequest(uri);
ServicePoint servicePoint = ServicePointManager.FindServicePoint(uri);
//should happen only once for each URL
servicePoint.Expect100Continue = false;
using (Stream stream = request.GetRequestStream())
using (StreamWriter writer = new StreamWriter(stream, Encoding.UTF8))
{
var call = new JsonRequest { Method = method, Id = 1, Params = args };
JsonConvert.Export(call, writer);
}
using (WebResponse response = GetWebResponse(request))
using (Stream stream = response.GetResponseStream())
using (StreamReader reader = new StreamReader(stream, Encoding.UTF8))
{
var jsonResponse = JsonConvert.Import<T>(reader);
try
{
// if no .HasError jumps to try where check for .Error is run
if (jsonResponse.HasError)
{
throw ReconstituteException(jsonResponse.Error);
}
else
{
return jsonResponse.Result;
}
}
catch (APINGException apiex)
{
if (!String.IsNullOrWhiteSpace(jsonResponse.ToString()))
{
if (!String.IsNullOrWhiteSpace(jsonResponse.Error.ToString()))
{
throw ReconstituteException(jsonResponse.Error);
}
}
throw new System.Exception("JsonRPCClient - Invoke Exception; APIING Exception; " + apiex.Message.ToString() + " - " + apiex.ErrorCode + " - " + apiex.ErrorDetails + "; JSON: " + JsonConvert.Serialize<JsonResponse<T>>(jsonResponse));
}
catch (System.Exception ex)
{
if (!String.IsNullOrWhiteSpace(jsonResponse.Error.ToString()))
{
throw ReconstituteException(jsonResponse.Error);
}
else
{
throw new System.Exception("JsonRPCClient - Invoke Exception; No HasError Propety; " + ex.Message.ToString() + "; JSON: " + JsonConvert.Serialize<JsonResponse<T>>(jsonResponse));
}
}
}
}
private static System.Exception ReconstituteException(Api_ng_sample_code.TO.Exception ex)
{
var data = ex.Data;
// API-NG exception -- it must have "data" element to tell us which exception
var exceptionName = data.Property("exceptionname").Value.ToString();
var exceptionData = data.Property(exceptionName).Value.ToString();
return JsonConvert.Deserialize<APINGException>(exceptionData);
}
}
}
So if you called JSONRpcClient to get a market you would then have to use the de-serialise method to convert the JSON back into a Class which can then be used and accessed easily e.g
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Newtonsoft.Json;
namespace Api_ng_sample_code.TO
{
public class CancelExecutionReport
{
public CancelExecutionReport()
{
ErrorCode = ExecutionReportErrorCode.OK;
}
[JsonProperty(PropertyName = "status")]
public ExecutionReportStatus Status { get; set; }
[JsonProperty(PropertyName = "errorCode")]
public ExecutionReportErrorCode ErrorCode { get; set; }
[JsonProperty(PropertyName = "marketId")]
public string MarketId { get; set; }
[JsonProperty(PropertyName = "instructionReports")]
public IList<CancelInstructionReport> InstructionReports { get; set; }
[JsonProperty(PropertyName = "customerRef")]
public string CustomerRef { get; set; }
public override string ToString()
{
var sb = new StringBuilder()
.AppendFormat("{0}", "CancelExecutionReport")
.AppendFormat(" : Status={0}", Status)
.AppendFormat(" : ErrorCode={0}", ErrorCode)
.AppendFormat(" : MarketId={0}", MarketId)
.AppendFormat(" : CustomerRef={0}", CustomerRef);
if (InstructionReports != null && InstructionReports.Count > 0)
{
int idx = 0;
foreach (var instructionReport in InstructionReports)
{
sb.AppendFormat(" : InstructionReport[{0}]={{{1}}}", idx++, instructionReport);
}
}
return sb.ToString();
}
}
}
You may think having all this exra code speeds it up but it took ages to write.
Rob
April 25, 2021 at 12:14 am
Ok nice. Good. This is my cup of tea. I'll take a look and definitely reply. Maybe a few days
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
April 25, 2021 at 1:08 am
Well I am working on a procedural loop design that ensures there is only 20 groups (£2/10p =10p), and that each group has enough but not too many outcomes in to ensure profit overall and that the games with lower match outcomes (upsets), get sprinkled about amongst the accas as you can have 40 good teams in form, with high team scores, and match outcomes that all point to their winning of a game but then the upset will come in somehow, either a draw or a win by the underdog.
A cartesian join doesn't seem to work as I can't cross join every outcome with every other outcome as it returns far too many results 120+ with just 6 outcomes if I remember right.
If you can think of SET based way of doing that then I take my hat of to you!
April 30, 2021 at 7:53 pm
This project and my Forum project are basically in the same situation.
https://www.sqlservercentral.com/forums/topic/forum-api-project
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
May 8, 2021 at 10:55 pm
Sorry Steve but I am not sure whether I am supposed to glean something from that link you sent me about setting up groups and members or not.
Are you just saying that the project you started on that thread is in the same sort of position as this one or that you have some sort of solution in your briefcase or something?
I have been working on a solution which uses multiple Cross Joins, randomising the chance that a "match outcome" will be selected using it's score, 1 with higher scores, 1 with medium scores, 1 with lower scores and less selections and then a ding dong pure outsider acca which may never come in but if it it did would win £££££££ from just a 10p stake as its just a max of 25 selections of the lowest match outcome scores.
Still working on it. Have had a bit of hard time lately due to some bad side effects from the Astra vaccine and the meds they put me on afterwards. Basically go blind every time I stand up for 10 seconds and have a nice line of holes in my outside right leg fr0m blood clots, even my arse cheek hurts, so it hurts to sit down on my desk chair to code.
Need to get this done though as today I missed out on the best part of £200 from a 10p stake on an acca just because Leeds managed to beat Spurs and Dortmund beat RB Leipzig, two "unexpected" outcomes from the standard input I would have entered, so I need to be able to sprinkle these upsets across the accas.
Thanks for your help and ideas though. At least the flat horse racing is back in UK, even if we have no idea who is any good as a 3yr old yet and the Derby is only weeks away.
I really really hope they can sort something out with this COVID lockdown situation so I can go to Ascot though. I can't imagine the Queen would even bother visiting it now Duke of Ed is gone by her lonesome sitting in her own box with a hundred million ££ of horse stock running about for herself and the ITV team, we will see.
May 9, 2021 at 1:26 pm
Rob, sorry, yes.
Are you just saying that the project you started on that thread is in the same sort of position as this one or that you have some sort of solution in your briefcase or something?
For some reason it's hard to write about without the code in hand. In the other thread I'm going to propose an approach which would be very applicable in this case too. It would be helpful if you could take an interest over there 🙂 For what might be a long explanation the implementation is supremely straightforward. Thank you for posting the C# code above. I'll post more this week.
Happy Mothers Day! If I spend too much time on the computer today... More to come.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
May 9, 2021 at 7:19 pm
Well I will keep an eye out on that thread for anything that pops up.
That C# code was just one class out of dozens, and just one of the two JSON classes that can get data from an interface which serves both. If I posted all the code it would be a hell of a lot to get your head round and I am still getting my own bugs in my new cut down version for my local PC.
For some reason UK and US have different Mothers Day, ours was March 14th, a week after my Mum's birthday, lucky her!
Thanks for the reply
May 10, 2021 at 11:55 am
Are you using the .NET Framework or .NET Core? The code above references System.Web.Services which implies the full framework. Since you're migrating off of SOAP endpoints is this reference still necessary? In .NET Core the preferred library would be System.ServiceModel
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
May 11, 2021 at 12:12 am
I have done all the migrating, the BOT is fully working, just 100 x more code in the JSON version than the SOAP version.
I am current developing it in VS 2017 , and it's using C# and NET Framework 4.6.1. Although I can easily move it up to the latest .NET version when one comes out.
All standard code and really not neccessary to this problem as it's an SQL issue of generating the tables full of accas/groups of match outcomes that is the issue - not the Betfair BOT, which just takes an input from a stored proc of bets to place then loops through them, places them, updates prices and results, and there are special methods for doing scalping Trades and Place bets or handling partially matched bets where you don't get a fully matched bet inplay, so need to re-adjust the size of the opposite bet amount (done again in SQL) as the whole thing runs off a Windows Service, or did.
I haven't decided the way to go yet, at the moment I just have another console project that uses the DLL I created the BOT in and run it manually in the console when I want to get races, runners, odds, place bets, get results, and so on with commands to the DLL like "GETTODAYSRACES", "GETTOMORROWSRACES", "RUNPLACEBETS" and other comands that were being run from the service from timers before.
I will probably put it back in as a windows service with a small desktop Windows Form app with a Stop/Start button (for the service), that shows current bank, and liability and allows me to stop / start at will as I don't have servers at the moment so it will be running on a laptop and obviously I don't want my laptop running 24/7 365....until I can afford a server and run it without thinking about it.
At the moment the site I am scraping racing data from keeps changing their HTML, adding random letters and numbers into their classes and attributes to try and stop the scraping, or even putting it all in JSON but I have managed with my Regex to get round everything they have done so far. I have a number of proxies I can use if I ever get 403'd, random referers and user-agents, cookie pot storage and delays between scrapes so I am not hammering them - karmic scraping I call it as have been on both sides of the barrier, stopping BOTs from scraping info from my old job sites, and banning them by Forward/Reverse DNS checks and a whitelist table of allowed SERPS (GoogleBot,BING,Yahoo etc) and their agents, IP ranges and so on that gets updated whenever a new IP is used and confirmed by the 2 way DNS check automatically, had situations where people had set their DNS up to display Google info which if not checked both ways to validate servers would mean letting a scraper into the site OR if not using a whitelist of known Google IP ranges a possible ban, so a clever coder could get you to ban major SERPS from crawling your own site by just frauding their DNS settings and if you don't do a 2 way check to ensure the server IPs are correct you can easily block Google as a "spoofer". Loads of tecniques I had to learn really, which come in handy on the other side, but the issue here is the SQL not the C# as that is all done.
I just need to fill a table with the bets, as that is called on a timer to place them when required.
Thanks
May 18, 2021 at 7:59 am
This was removed by the editor as SPAM
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply