Help analysing data from Guardian in CSV file

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
JasBarrow
Posts: 138
Joined: Tue Sep 18, 2018 3:03 pm

Hi,

I'm using history lists within Guardian to store metrics (Market volume, VWAP & LTP) for each runner in the greyhound markets at 1 second intervals which I then analyse at he end of the day. Problem I'm having is because the csv file is not in tabular format (like a p&l file) it means I have to spend some time tabulating the data, which I do primarily by using the text to columns function. I've recorded a macro so I can repeat the steps for each file but this is very time consuming. It takes about a minute for the macro to go through the steps necessary to tabulate the data and then about another minute to analyse it. Because I'm doing this on greyhound markets its taking a few hours each evening to manually go through all the data.

I've tried using power query as its easier to separate the data but its even slower to arrange the data in tabular form than above. And its not worth the extra time because I'm not adding data from files together, I'm just analysing each csv individually.

My question is does anybody know a quicker way of doing this? I'm sure there are lots of other people who use Guardian in a similiar way to harvest data and are probably using more history lists than I am in this example so any tips, ideas as to how I can do this more efficiently would be really appreaciated.
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

JasBarrow wrote:
Fri Jun 04, 2021 10:24 am
Because I'm doing this on greyhound markets its taking a few hours each evening to manually go through all the data.
Can't you kick off some sort of end-of-day process overnight and look at the results the next day? ie Concatenate all your day's csv files and then run the macro on the whole day not market by market.

Using Notepad, create a file in your csv folder called something like JoinCSV.BAT and put the following in it....

del allcsv.csv
type *.csv >> allcsv.txt
ren allcsv.txt allcsv.csv

To join your csv files, run windows explorer and double click on the .BAT file you created above.
You should end up with one file called allcsv.csv that has all of the csvs in the one file.

Purists will probably suggest slight different ways but that should get you started.
Atho55
Posts: 637
Joined: Tue Oct 06, 2015 1:37 pm
Location: Home of Triumph Motorcycles

Can you post pics of what you start with and what you want it to look like
JasBarrow
Posts: 138
Joined: Tue Sep 18, 2018 3:03 pm

ShaunWhite wrote:
Fri Jun 04, 2021 3:22 pm
JasBarrow wrote:
Fri Jun 04, 2021 10:24 am
Because I'm doing this on greyhound markets its taking a few hours each evening to manually go through all the data.
Can't you kick off some sort of end-of-day process overnight and look at the results the next day? ie Concatenate all your day's csv files and then run the macro on the whole day not market by market.

Using Notepad, create a file in your csv folder called something like JoinCSV.BAT and put the following in it....

del allcsv.csv
type *.csv >> allcsv.txt
ren allcsv.txt allcsv.csv

To join your csv files, run windows explorer and double click on the .BAT file you created above.
You should end up with one file called allcsv.csv that has all of the csvs in the one file.

Purists will probably suggest slight different ways but that should get you started.
Hi Shaun,

The reason I do it one by one is because I want to analyse the data from each market individually. If I join them all together then I'll be left with a total sum rather than the individual totals of each race.
JasBarrow
Posts: 138
Joined: Tue Sep 18, 2018 3:03 pm

Atho55 wrote:
Fri Jun 04, 2021 3:29 pm
Can you post pics of what you start with and what you want it to look like
Hi Atho,

So the first image is the raw CSV file downloaded from Guardian and the second image is how I want it to look. I get it to look this way by using copying the CSV file and using Text to Columns, Find and Replace & then copying the each individual column to a new sheet. I'm sure there must be a quicker way!
You do not have the required permissions to view the files attached to this post.
Atho55
Posts: 637
Joined: Tue Oct 06, 2015 1:37 pm
Location: Home of Triumph Motorcycles

Is there any reason why the bottom pic has no column titles? It`s hard to visualise how the top fits to the bottom
JasBarrow
Posts: 138
Joined: Tue Sep 18, 2018 3:03 pm

There jus two random files I grabbed and posted a pic of but essentially the top pic is HL1 is LTP, HL2 is runner volume and HL3 is market volume.

Bottom then is from left to right Time, Market Volume and then LTP for all 6 runners and runner volume for all 6 runners so 5.1 is LTP for runner 1, 16.5 LP for runner 2 etc...
Atho55
Posts: 637
Joined: Tue Oct 06, 2015 1:37 pm
Location: Home of Triumph Motorcycles

If it was me I would put ALL Hist List 1 in its own list, same with Hist 2 and 3. Then do a VLOOKUP on the Runner name to pull data together or the time. Creating Pivot tables is easy once it`s pulled together as a list. You are trying to sew three bits of data together where a Pivot would do that easier. Good Luck
JasBarrow
Posts: 138
Joined: Tue Sep 18, 2018 3:03 pm

Atho55 wrote:
Fri Jun 04, 2021 6:59 pm
If it was me I would put ALL Hist List 1 in its own list, same with Hist 2 and 3. Then do a VLOOKUP on the Runner name to pull data together or the time. Creating Pivot tables is easy once it`s pulled together as a list. You are trying to sew three bits of data together where a Pivot would do that easier. Good Luck
Only catching up now on the thread...

Thanks Atho that sounds like something I could do, maybe I'm overcomplicating this!
JasBarrow
Posts: 138
Joined: Tue Sep 18, 2018 3:03 pm

How does everybody else analyse data? I'm sure there are lots of people using Guardian and history lists to gather data and then analyse it at the end of the day.

Would love to hear how everybody else approached this.
User avatar
bigpapaperry
Posts: 18
Joined: Sat Feb 13, 2021 1:18 pm

https://spreadsheeto.com/text-functions/

THe Left, Right or Mid function lets you get data from any string
i use this which is not very hard just takes a few practices to see what is does and what you need then it would take you seconds to convert from pic 1 to pic 2
User avatar
nikolong
Posts: 18
Joined: Thu Nov 30, 2017 8:27 pm

I have 3 or 4 parameters that I use, I store them at post time, using stored values like you. some I get from doing a small calculation from the appropriate data. I export them to a spreadsheet and it only takes about ten minutes to tidy everything up and put the profit and losses in. Maybe you could cut back on the amount of stored values that you are using?
Niko
Atho55
Posts: 637
Joined: Tue Oct 06, 2015 1:37 pm
Location: Home of Triumph Motorcycles

Jas

If you search for NigelK trading record(er) I recall that it had a sheet within it that extracted elements inline with what you are looking to do. That might help but =LEFT() =MID() =RIGHT() are handy to use
JasBarrow
Posts: 138
Joined: Tue Sep 18, 2018 3:03 pm

Thanks guys some more food for thought
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”