% Change excel query

[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.

[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.

[Rinconpaul]

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.

[Toil]

Yer, like I said probably not what your after :)

[jazzy]

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>[table_name]
GROUP BY event_id, selection

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

Or you could import the spreadsheet into a database and then run the query.</table_name>

[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.

[beton]

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.

[Rinconpaul]

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?

[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/static/tutorial-window.html
or
http://sqlmag.com/sql-server-2012/how-use-microsoft-sql-server-2012s-window-functions-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.

[Rinconpaul]

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/static/tutorial-window.html
or
http://sqlmag.com/sql-server-2012/how-use-microsoft-sql-server-2012s-window-functions-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 :)

[The Ocho]

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?

[walkermac]

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

[Puntz]

Golden Sally

5.3 Cell:J23
8 CellJ2


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

50.94%


'I think

[Rinconpaul]

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.

[beton]

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.

[Rinconpaul]

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.

[Shaun]

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

[beton]

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

[jazzy]

The SQL method (postgres):


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

[The Ocho]

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

[Puntz]

Diamond Charlie 3.1 11:59:44 AM

Diamond Charlie 2.5 12:24:08 PM


=(J36-$J58)*100/$J58

-19.35%

This has been around since 1992 when it's done with every price update in the field, the controversial, "smart money" system.

The problem became, when progressively everyone cottoned onto it, by 2000 it became so obvious with late plunges and threw the game way off track.

"smart money" systems became as useful as fools gold.

To get around it, less bets and refine the "when" factor.

Then betfair came along, changed the plot yet again.

It's now more or less a guide rather than the rule.

[Rinconpaul]

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

Good question TO. I looked at a weeks Gallops, 3170 races and 1293 closed lower than Open, 40% near enough....or is that 140%?... who knows lol :)

[The Ocho]

Good question TO. I looked at a weeks Gallops, 3170 races and 1293 closed lower than Open, 40% near enough....or is that 140%?... who knows lol :)
Are you talking about the favourite in each race or the whole field?

[Rinconpaul]

Are you talking about the favourite in each race or the whole field?

Out of all horses in the race, 40% close lower than Open.

[mattio]

Are you taking into account the bookmaker percentages for the open and the close? If you aren't then your data is next to useless as more often than not the price change is a reflection on the change in bookmaker percentage from say 140% at open to 115% at close as an example.

[Rinconpaul]

Are you taking into account the bookmaker percentages for the open and the close? If you aren't then your data is next to useless as more often than not the price change is a reflection on the change in bookmaker percentage from say 140% at open to 115% at close as an example.

Technically you are correct mattio, and I used to agonise over such things ONCE!

I remember being sent a lot of historical data by a Master punter. He built his methods and systems around 'AS FOUND" (mistakes, misrepresentations, come what may data) not corrected data. If they made a profit based on that, then so be it.

I fought the logic for some time until I realised that his logic had merit, coz I knew he was doing OK, so I threw of the shackles of logical wisdom and adopted the logic of, "don't change anything if ain't broken wisdom.

Like the bit of fun I've been having with, is it 40% or 140%, who cares, as long as it makes money, use either figure... lol

[darkydog2002]

The trouble with "shorteners" is that one never knows whether its Mug Money
or Informed Money so using that as a basis for making money is worst than useless.

[beton]

Are you taking into account the bookmaker percentages for the open and the close? If you aren't then your data is next to useless as more often than not the price change is a reflection on the change in bookmaker percentage from say 140% at open to 115% at close as an example.In the month of Oct 2014 1216 races and 11666 horses. 375 races won by horses that shortened as SP-OP. Overall 2072 horses shortened, 2180 stayed the same. 2123 horses ranked better and 7297 horses stayed the same rank.

But the true equation, as Mattio has raised, is whether the horses probability increased(shortened) or decreased (drifted) in a 100% market. 9175 decreased, and 28 stayed the same.

The real question is how many really shortened ie a significant change of probability.

[mattio]

The other issue with this too is the on track "opening" price is not the true opening price, the true opening price is what the corporates and TAB open their fixed odds betting at. The on track opening prices are usually a consensus of what the market has done since the bookmakers opened.

A prime example here is a bet I had on Sunday at Ararat, in R5 the 2nd placed horse Alternative Choice opened on track at $4.60 and the SP was $4.00, I took $13 win and $3.75 place on this horse the day before when prices first came up thanks to a massive error from one of the corporates. The average opening price of the 3 corporates that put up the early prices was about $9 which is a long way from $4.60 on track opening price.