Smartgambler
Pro-Punter

Go Back   OZmium Sports Betting and Horse Racing Forums > Public Forums > General Topics
User Name
Password
Register FAQ Search Today's Posts Mark all topics as read

To advertise on these
forums, e-mail us.

Reply
 
Thread Tools Search this Thread Display Modes
  #11  
Old 6th November 2014, 09:34 PM
The Ocho The Ocho is offline
Member
 
Join Date: Aug 2010
Posts: 1,037
Default

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
Reply With Quote
  #12  
Old 6th November 2014, 11:46 PM
walkermac walkermac is offline
Member
 
Join Date: Nov 2013
Posts: 596
Default

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
Reply With Quote
  #13  
Old 7th November 2014, 01:33 AM
Puntz Puntz is offline
Member
 
Join Date: Jan 2012
Posts: 291
Default

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.
Reply With Quote
  #14  
Old 7th November 2014, 04:08 AM
Rinconpaul Rinconpaul is offline
Banned
 
Join Date: Feb 2013
Posts: 755
Thumbs up

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
File Type: xlsx % Change Query.xlsx (17.3 KB, 784 views)
Reply With Quote
  #15  
Old 7th November 2014, 06:29 AM
beton beton is offline
Member
 
Join Date: Jan 1970
Posts: 589
Default

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.
Reply With Quote
  #16  
Old 7th November 2014, 06:55 AM
Rinconpaul Rinconpaul is offline
Banned
 
Join Date: Feb 2013
Posts: 755
Default

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.
Reply With Quote
  #17  
Old 7th November 2014, 11:03 AM
Shaun Shaun is offline
Member
 
Join Date: Jan 1970
Location: Western Australia
Posts: 3,456
Default

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
_______________________________________________
Reply With Quote
  #18  
Old 7th November 2014, 11:44 AM
beton beton is offline
Member
 
Join Date: Jan 1970
Posts: 589
Default

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
Reply With Quote
  #19  
Old 7th November 2014, 08:52 PM
jazzy jazzy is offline
Member
 
Join Date: Nov 2010
Posts: 49
Default

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
Reply With Quote
  #20  
Old 7th November 2014, 09:31 PM
The Ocho The Ocho is offline
Member
 
Join Date: Aug 2010
Posts: 1,037
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is On
Forum Jump



All times are GMT +10. The time now is 10:21 PM.


Powered by: vBulletin Version 3.0.3
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
©2008 OZmium Pty. Ltd. All rights reserved . ACN 091184655