OZmium Sports Betting and Horse Racing Forums

OZmium Sports Betting and Horse Racing Forums (http://forums.ozmium.com.au/index.php)

- General Topics (http://forums.ozmium.com.au/forumdisplay.php?f=59)

- - % Change excel query (http://forums.ozmium.com.au/showthread.php?t=29284)

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?

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

Golden Sally

5.3 Cell:J23
8 CellJ2

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

50.94%

'I think

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.

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.

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.

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

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

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

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