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.
Help analysing data from Guardian in CSV file
- ShaunWhite
- Posts: 9731
- Joined: Sat Sep 03, 2016 3:42 am
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,ShaunWhite wrote: ↑Fri Jun 04, 2021 3:22 pmCan'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.
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.
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.
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...
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...
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...Atho55 wrote: ↑Fri Jun 04, 2021 6:59 pmIf 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
Thanks Atho that sounds like something I could do, maybe I'm overcomplicating this!
- 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
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
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
Niko
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
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