OZmium Sports Betting and Horse Racing Forums

#11

6th November 2014, 09:34 PM

The Ocho

Member

Join Date: Aug 2010

Posts: 1,037

Quote:

Originally Posted by Rinconpaul

I'm after some help on applying a formula to a database, that returns the % change in Odds from Open to Close. Maybe walkermac, Excel Cup 2014 winner, could help?

From the attachment, you have varying numbers of fluctuations for each runner.
For example:
Golden Sally Open = 5.3, Close 8. Therefore % change = 150%
Nuclear Power Open = 9.8, Close = 14. Therefore % change = 143%

I could put the database into a pivot table but there's no standard function to carry out this calculation, only Sum, Count, Average, Max, Min, Product, Stdev & Var.

This new function if possible (hopefully not in VBA) would allow me to quickly scan large databases and look for correlations in steamers/drifters and their strike rates.

Thanks in anticipation.

Since when do % changes equal what you say here? Surely they are 50% and 43% changes. A 100% change would be double the price, wouldn't it? Or am I missing something?

__________________
Never give up on a dream just because of the time it will take to accomplish it. The time will pass anyway.”

― Earl Nightingale

#12

6th November 2014, 11:46 PM

walkermac

Member

Join Date: Nov 2013

Posts: 605

Like last time, an inelegant solution; but it doesn't use arrays or SQL or anything fancy.

In cell S2 (and down), if this is the close price, display the percentage difference between the opening price and the closing price:
=IFERROR(IF(ROW()=T2,INDIRECT("j"&T2)/INDIRECT("j"&U2),""),"")

In cell T2 (and down), we store the row number of the horse's opening price:
=IFERROR(IF(H2="","",IF(NOT(H2=H1),ROW(),T1)),"")

In cell U2 (and down), we store the row number of the horse's closing price:
=IF(T2=T3,U3,ROW())

So in cells S2 through to U2, it displays the following values:
1.509433962 2 23

Last edited by walkermac : 6th November 2014 at 11:47 PM. Reason: reword for clarity

#13

7th November 2014, 01:33 AM

Puntz

Member

Join Date: Jan 2012

Posts: 292

Golden Sally

5.3 Cell:J23
8 CellJ2

=(J2-$J23)*100/$J23

50.94%

'I think

Last edited by Puntz : 7th November 2014 at 01:38 AM.

#14

7th November 2014, 04:08 AM

Rinconpaul Rinconpaul is offline

Banned

Join Date: Feb 2013

Posts: 755

TO and Puntz, you're right in what you say, but the reason behind it is that when you have a steamer, the answer would be a negative %. This way anything under a 100% is a steamer and over a drifter. As long as the user is aware, but very observant and I'm glad you're paying attention in class," up the back there"...lol

I've added an example of a steamer to the spreadsheet.

Walkermac, thanks mate, you're a true artisan of the excel craft, and your champion crown remains intact, without challenge

Case closed, many thanks.

Attached Files

% Change Query.xlsx (17.3 KB, 949 views)

#15

7th November 2014, 06:29 AM

beton

Member

Join Date: Jan 1970

Posts: 589

Interesting angle. Very opportune for me as I was just having a web scraper updated for the oncourse bookies data. I had added open price rank, Fluc count,SP rank. Now I have added price difference, rank difference and probability difference. I am noticing that although there there may or may not be a price difference, the probability has altered differently as the market has come from a 145% opening market down to a 120% starting market. To get the probability difference I converted to 100% in both the OP and SP market. Very early days yet as I am just using the trial version but a simple scan is showing things in a clearer light.
To the point, you may want to consider adding more if possible before you start the download and analysis stage.

#16

7th November 2014, 06:55 AM

Rinconpaul Rinconpaul is offline

Banned

Join Date: Feb 2013

Posts: 755

Shhhh!!!.....don't give everything away Wally

Mate, rather than web scrape, have you tried Dynamic Odds. You get all that for a $1 a day and downloadable to excel (not that I've been able to achieve that yet, but that's another story, email me) plus years of historical data.

#17

7th November 2014, 11:03 AM

Shaun

Member

Join Date: Jan 1970

Location: Western Australia

Posts: 3,457

I am confused, is this supposed to be a live events feed or just as a database?

__________________
One Drive

"If the corporates are treating you poorly , just go elsewhere."
"If they need you , they will soon find out."
"If you need them , you will soon find out." --moeee
_______________________________________________

#18

7th November 2014, 11:44 AM

beton

Member

Join Date: Jan 1970

Posts: 589

Quote:

Originally Posted by Shaun

I am confused, is this supposed to be a live events feed or just as a database?

Database initially but live later

#19

7th November 2014, 08:52 PM

jazzy

Member

Join Date: Nov 2010

Posts: 49

The SQL method (postgres):

Code:


 CREATE TABLE test(
 	a_pkey SERIAL NOT NULL PRIMARY KEY,
 	event_id VARCHAR(12) NOT NULL,
 	full_description VARCHAR(100) NOT NULL,
 	course VARCHAR(12) NOT NULL,
 	scheduled_off VARCHAR(12) NOT NULL,
 	event VARCHAR(36) NOT NULL,
 	actual_off VARCHAR(12) NOT NULL,
 	tabno SMALLINT NOT NULL,
 	selection VARCHAR(36) NOT NULL,
 	settled_date DATE NOT NULL,
 	odds REAL NOT NULL,
 	latest_taken VARCHAR(12) NOT NULL,
 	first_taken VARCHAR(12) NOT NULL,
 	in_play VARCHAR(12) NOT NULL,
 	number_bets INTEGER NOT NULL,
 	volume_matched REAL NOT NULL,
 	sports_id VARCHAR(12) NOT NULL,
 	selection_id VARCHAR(12) NOT NULL,
 	win_flag SMALLINT NOT NULL
 );	
 	
 
 
 COPY test (event_id, full_description, course, scheduled_off, event, actual_off, tabno, selection, settled_date, odds, latest_taken, first_taken, in_play, number_bets, volume_matched, sports_id, selection_id, win_flag)
 FROM 'E:/downloads/change.csv'
 csv
 
 SELECT DISTINCT full_description, event, selection, win_flag, first_value(odds) OVER w / last_value(odds) OVER w AS chg
 FROM test
 WINDOW w AS (PARTITION BY selection_id ORDER BY a_pkey RANGE
 			BETWEEN UNBOUNDED PRECEDING
 			AND UNBOUNDED FOLLOWING)
 
 
 "Coro (AUS) 7th Jul";"R1 1000m Mdn";" Nuclear Power";0;1.42857
 "Coro (AUS) 7th Jul";"R1 1000m Mdn";" Diamond Charlie";0;0.806452
 "Coro (AUS) 7th Jul";"R1 1000m Mdn";" Golden Sally";1;1.50943

#20

7th November 2014, 09:31 PM

The Ocho

Member

Join Date: Aug 2010

Posts: 1,037

No worries RP. Just as an aside, do most horses shorten or lengthen from open to close or is it just 50/50?

__________________
Never give up on a dream just because of the time it will take to accomplish it. The time will pass anyway.”

― Earl Nightingale

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

	To advertise on these forums, e-mail us.