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
  #1  
Old 6th November 2014, 09:30 AM
Rinconpaul Rinconpaul is offline
Banned
 
Join Date: Feb 2013
Posts: 755
Wink % Change excel query

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.
Attached Files
File Type: xlsx % Change Query.xlsx (12.7 KB, 1161 views)
Reply With Quote
  #2  
Old 6th November 2014, 11:47 AM
Toil Toil is offline
Member
 
Join Date: Nov 2010
Posts: 89
Default

Hi Rinconpaul,

I know this gives you a percentage change =(A1/B1)-1

But probably not what your after.

Shaun or someone would definitely be able to help you I'm sure.
Reply With Quote
  #3  
Old 6th November 2014, 11:52 AM
Rinconpaul Rinconpaul is offline
Banned
 
Join Date: Feb 2013
Posts: 755
Default

Quote:
Originally Posted by Toil
Hi Rinconpaul,

I know this gives you a percentage change =(A1/B1)-1

But probably not what your after.

Shaun or someone would definitely be able to help you I'm sure.


Thanks Toil. Yes it's easy enough to work out on a case by case basis, the difficulty is the number of rows for each runner is forever changing, so the formula needs to be intuitive and know when it's a different runner and be able to count back to the beginning to find out what the first odds value was. It needs to be automatic, as tens of thousands of entries to assess.
Reply With Quote
  #4  
Old 6th November 2014, 12:22 PM
Toil Toil is offline
Member
 
Join Date: Nov 2010
Posts: 89
Default

Yer, like I said probably not what your after
Reply With Quote
  #5  
Old 6th November 2014, 01:20 PM
jazzy jazzy is offline
Member
 
Join Date: Nov 2010
Posts: 49
Default

If you can get MS Query to work (I tried but it doesn't want to play nice) you could do this using SQL - something like:

SELECT MAX(course), MAX(event), (MAX(odds) / MIN(odds)) FROM [table_name]
GROUP BY event_id, selection

--------------

Or you could import the spreadsheet into a database and then run the query.

Last edited by jazzy : 6th November 2014 at 01:23 PM.
Reply With Quote
  #6  
Old 6th November 2014, 01:28 PM
jazzy jazzy is offline
Member
 
Join Date: Nov 2010
Posts: 49
Default

Just re-read your question. the above won't do what you asked...

I'd need to have a think whether a single SQL statement will do it.
Reply With Quote
  #7  
Old 6th November 2014, 01:46 PM
beton beton is offline
Member
 
Join Date: Jan 1970
Posts: 589
Default

Quote:
Originally Posted by jazzy
Just re-read your question. the above won't do what you asked...

I'd need to have a think whether a single SQL statement will do it.
The answer will need to be in excel along the lines of MATCH (event) MAX and MIN. The data files are downloaded month at a time in excel.
Reply With Quote
  #8  
Old 6th November 2014, 01:53 PM
Rinconpaul Rinconpaul is offline
Banned
 
Join Date: Feb 2013
Posts: 755
Default

Quote:
Originally Posted by beton
The answer will need to be in excel along the lines of MATCH (event) MAX and MIN. The data files are downloaded month at a time in excel.


Yeah sort of Wally, except not MAX and MIN: MATCH (event) LOOKUP (first row in subset odds value) divided by (last row in subset odds value)*100.

Maybe an Array?
Reply With Quote
  #9  
Old 6th November 2014, 03:58 PM
jazzy jazzy is offline
Member
 
Join Date: Nov 2010
Posts: 49
Default

Righto, it can be done with a single SQL query, but it would need to use the new FIRST_VALUE and LAST_VALUE SQL window functions.

eg:
http://www.postgresql.org/docs/9.3/...ial-window.html
or
http://sqlmag.com/sql-server-2012/h...unctions-part-1

I've never used them before, so I'd need to play around with it to get it to work, and I doubt whether MS query would support it, you'd need to import the data into a ridgy-didge SQL database.

If you can't get excel to do it, this is the way I'd go.
Reply With Quote
  #10  
Old 6th November 2014, 04:23 PM
Rinconpaul Rinconpaul is offline
Banned
 
Join Date: Feb 2013
Posts: 755
Default

Quote:
Originally Posted by jazzy
Righto, it can be done with a single SQL query, but it would need to use the new FIRST_VALUE and LAST_VALUE SQL window functions.

eg:
http://www.postgresql.org/docs/9.3/...ial-window.html
or
http://sqlmag.com/sql-server-2012/h...unctions-part-1

I've never used them before, so I'd need to play around with it to get it to work, and I doubt whether MS query would support it, you'd need to import the data into a ridgy-didge SQL database.

If you can't get excel to do it, this is the way I'd go.


Call me dumb or call me dumber....lol but I don't even know what SQL is?
Better to stick with excel. Cheers for your ideas though jazzy
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 04:44 AM.


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