How to discover profitable automation strategies?

[firlandsfarm]

This is very helpful. Thanks.

My spreadsheets are 160000 rows deep with many sumif and vlookups and whatnot and are about to break my machine. YOu make it sound simple.

You're welcome, it's nice to have the opportunity to return the help I have been given by forumites.

My racing database is 2.2 million rows * 50 columns in the largest table, the Horse-in-Race table that stores all the race specific data for each horse in a race. It's supported by tables for the Races data, Jockies, Trainers, Going, (non-race specific) Horse data, etc. etc. but it can sort and display in a few seconds.

One thing you will learn with a real database is the use of relational tables and indexes. Indexes tell the software where a record is in a table so that it doesn't have to go through all data row by row to find stuff. You don't index everything just the columns you usually search on. Relational tables (referred to as 'Normalisation' in database world) are where you have a table for each field with common multiple entries. So in my example the Horse-in-Race table will refer to the ID of the horse in the Horses table so for each Horse-in_Race entry I store the ID number of each runner from the Horses table and that gives me the name, sex, DOB, Sire, Dam etc. of the Horse for every race it runs in while only storing that data once. Relational tables may sound more complicated than Excel but once you start thinking of data in categories it falls into place and allows you to apply filters more efficiently. You can of course still do an all encompassing single table while you think of how to design the data!

[abgespaced]

Hi abgespaced you sound like my kinda guy, I think you are talking of operating similar to how I do.

Thanks my dude! Great points about Access. I'll look into it. Anything that can make the process easier.

Question - how do you move the data into Access in the first place? Does BA have an import feature like it does for Excel?

[abgespaced]

Might have to look at Access and SQL again (years ago dabble) . The thing that is breaking my brain today is whether to extend my 50% stake/profit take out from 4 ticks or to extend the pull back threshold to x ticks so that I don't get shaken out after taking profit by a short term move. It hurts!
Edit: Or both..see it hurts!

Can't you run separate tests for both?

[firlandsfarm]

Question - how do you move the data into Access in the first place? Does BA have an import feature like it does for Excel?

Access can only be used as an archiving app. it does not replace Excel as a data capture alternative. Some of my uses are to download the Betfair BSP files monthly, merge them into one file and then import them into Access for assessment. Likewise I use some of the data capture worksheets available in the forum to capture market prices and then link the sheet to Access for import and assessment. Basically any data you can get as a text or Excel file or you can copy from a website page and paste into Excel you can then import into Access. I don't know if you use the football data at Football-Data.co.uk but you can download the football data as an Excel file and import it.

The link between Access and Excel goes both ways so I harvest data in Excel, import it to Access/SQL, query the data in Access and sometimes link the extracted data back to Excel if it can process the extraction more efficiently. For example Access does not have a graph facility. They can be linked such that they work in parallel using the one most suited to your needs.

[ShaunWhite]

Access and SQL are great but quite a learning curve if you're not used to that world. But there's a perfectly adequate way to do it using just excel.

Basically, it's never going to be necessary to access all of your data at one time because that would just encourage backfitting. Ie you'll always just be using in and out of sample data.

With that in mind you could organise your raw data into quarterly chunks (or some other subdivision). Then rather than your analysis sheet containing data it just contains references to cells in a file called something like EnqData. So when you want to run your analysis on different sets of data just rename the appropriate archive file to EnqData and it'll be pulled into the analysis sheet. (or you could find/replace the formulas in Excel to point at a different source, either is fine)

The premise is to seperate your archive from your reporting databases because that's always going to be smaller than your entire dataset.

It's not ideal and adds a small extra process, but it's easy, familiar and also speeds up your analysis because the sheet doing all your analysis only contains the minimum amount of data you want for any given job. Also you can easily swap say UK horses for Aus horses or even dogs and run the same tests.

A huge monolithuc database isn't necessary and even though I use SQL my individual physical data files only contain 1 sport each for just 1 day each. For analysis I just pull the days in that I need (maybe even or odd numbered days or 6 random months out of 12) into my EnquiryData.mdb. The reason my archive is spilt is because I collect almost 2gb of data a day, that very quickly becomes a ridiculously unwieldy file esp for backups etc.

[ShaunWhite]

... that all sounds harder than it actually is. Ask if anything doesn't make sense. Basically just 3 things. An archive, an enquiry db, and an analysis sheet. All seperate entities.

[spreadbetting]

2gb of data a day

That's hell of a lot of data, I still collect the Betfair SP files pretty much out of habit as it's automated, used to look at the IPMIN MAX data like a lot of people but that goes back to 2008 and is only 621Mb.

Do you find it useful or is it a case of getting everything in case one day you might need it?

[ShaunWhite]

Do you find it useful or is it a case of getting everything in case one day you might need it?

Thing about data is you can't predict what you'll need in the future and it's sods law you haven't got it when you do. So I'm a hoarder.
It's tick data so gets stupidly big pretty quick. What I don't have and slightly regret is supplementary data that's not from Betfair such as race class and all runner fundamentals.

I don't use data more than a year old very often. If something hasn't made a $ in the last 12 months it's not worth going back any further. Data degrades anyway and I can't be bothered to get into weighing data significance by age and all that.

[Derek27]

I'm the opposite of you Shaun. I always keep hard drive backups but too quick to permanently delete data (especially emails) that I later need.

[ShaunWhite]

I'm the opposite of you Shaun. I always keep hard drive backups but too quick to permanently delete data (especially emails) that I later need.

Manual vs auto init. Your edge is is in your head, mine is in an SSD.

[Atho55]

Only advice I could add would be to particularise the data you want to compile to make the future search easier and quicker. You can dispense with splitting your data into blocks and the Slicer feature in Excel lets you select your criteria via a button style interface.

Strategy Search.jpg

The screenshot, Sandown, September, Rank 5 as sorted by BSP. You can now consider how likely it is to do well tomorrow or at any other Sandown meetings this month. A small step forward from a stab in the dark.

[wearthefoxhat]

Only advice I could add would be to particularise the data you want to compile to make the future search easier and quicker. You can dispense with splitting your data into blocks and the Slicer feature in Excel lets you select your criteria via a button style interface.

Strategy Search.jpg


The screenshot, Sandown, September, Rank 5 as sorted by BSP. You can now consider how likely it is to do well tomorrow or at any other Sandown meetings this month. A small step forward from a stab in the dark.

Nice tables.

I could spend some time testing out some lay data on that spready.

[firlandsfarm]

Access and SQL are great but quite a learning curve if you're not used to that world. But there's a perfectly adequate way to do it using just excel.

I agree on SQL Shaun, the most pernickety language I have ever had the misfortune to use but no competitor! But Access? It's drag-and-drop! I didn't find any problem getting stuck in on day one! And you can start to dabble with SQL while cruising on Access.

[Derek27]

Access and SQL are great but quite a learning curve if you're not used to that world. But there's a perfectly adequate way to do it using just excel.

I agree on SQL Shaun, the most pernickety language I have ever had the misfortune to use but no competitor! But Access? It's drag-and-drop! I didn't find any problem getting stuck in on day one! And you can start to dabble with SQL while cruising on Access.

Create a simple query with two or three connecting tables, add a few conditions or filters, click on the SQL tab and the code looks absolutely horrendous.

[firlandsfarm]

Access and SQL are great but quite a learning curve if you're not used to that world. But there's a perfectly adequate way to do it using just excel.

I agree on SQL Shaun, the most pernickety language I have ever had the misfortune to use but no competitor! But Access? It's drag-and-drop! I didn't find any problem getting stuck in on day one! And you can start to dabble with SQL while cruising on Access.

Create a simple query with two or three connecting tables, add a few conditions or filters, click on the SQL tab and the code looks absolutely horrendous.

But why look at the code if you are a newbie to the technology? It will work perfectly from the drag-and-drop design screen! You don't need to know how an internal combustion engine works to be able to drive a car.