Handling Automation Reports

[firlandsfarm]

If you check the box to "Export reports on market closure" you will know that a Log of your Automation activity, details of matched bets and profit and loss files are sent to the Market Reports folder as individual files per market and what a time consuming pain it is to manage those files one at a time.

You may also know that if you use DOS code ...

copy [folder location]\*.csv [folder location]\[combined file name].csv

... then DOS will combine all the single files in the folder into one larger file but you lose the race details and allocating the race details to the action/runner can be just as much a pain! But I just came across the following command line to be used in the appropriate Report folder.

findstr "^" *.csv >[Combined file name].csv

So to use you would go to (or preface the command with) the location of your Reports folder down to the [date]/[type of report] in the Command Prompt. This command will include the name of the individual files created by BA before each line so when you open the combined file in Excel the first column for each row is the file name. For example here is an extract from a merge on my Matched Bets reports of yesterday …

MergedReports.zip

Don't ask what I was doing, just experimenting with a theory! Anyway I hope this is of help to some.

[Vovsen]

Very nice, I could really use this.

I was fiddling with it for a bit and could not get it to work.

This is the run command i did:

find "^" *.csv >C:\Users\Bruger\AppData\Roaming\Bet Angel\Bet Angel Professional\MarketReports\25_07_2020\MatchedBets.csv


Maybe I misunderstood, but isn't this the only command to run to get a similar extract as your?


I will try again when I have more time tomorrow

[firlandsfarm]

This is the run command i did:

find "^" *.csv >C:\Users\Bruger\AppData\Roaming\Bet Angel\Bet Angel Professional\MarketReports\25_07_2020\MatchedBets.csv

Sorry, I probably didn't explain it well, what makes sense just after you have done something rarely makes sense to someone else just coming across the explanation! So here's what I have just done step by step …

Open the Command Prompt window

Change the active folder to the folder of the reports you want to combine. So for the "Matched Bets" reports (and assuming everything is C: drive related) it would be ...

cd\Users\[user]\AppData\Roaming\Bet Angel\[name of user data folder for installation]\MarketReports\25_07_2020\MatchedBets

Run the command: findstr "^" *.csv >[Combined file name].csv

Note: nothing happens in the Command Prompt window but go look inside the chosen report folder and the new file should be there.

Combined.csv.png

Here is my Command Prompt window after running the process …

CombiningReportFiles.png

… where my windows username is "idc10", the BA installation reports folder is "BetAngel_v1.55_0 - Inst01" (I have multiple installations of BA and like to notate them with the version No. and Installation No.) and the reports are in the file "Combined.csv". The "Combined.csv" file will appear in the "MarketReports\25_07_2020\MatchedBets" folder as above.

Let me know how you get on.

[firedave]

Nice one!

It would be good if it were possible for BA to do this as Betfair have messed up the P&L reporting at the moment. I'm finding I'm doing a lot of work with these files.
I was very close to writing a python script to do something similar but this is so simple and gets 80% of the way there.

Thanks!

[ShaunWhite]

Open the Command Prompt window....

Stick all those commands in a file called AnythingYouWant.BAT When you double click it, it will execute the commands.
eg.
cd c:\Users\[user]\AppData\Roaming\Bet Angel\[name of user data folder for installation]\MarketReports\25_07_2020\MatchedBets
findstr "^" *.csv >[Combined file name].csv

You can also setup these batch files (.BAT) to run from your Windows task Scheduler for those regular end of day jobs like achiving.

If you run command.com and type "SET" and press [enter], you will see a list of all your Windows environment variables, any of these can be used in batches if you prefix and suffix them with a % sign to save you from typing/typos. Or if you log on as a different user perhaps for using a test environment they still work as they pick up the right username....and it's just good practice.

eg cd C:\Users\%USERNAME%\AppData\Roaming\Bet Angel\[name of user data folder for installation]\MarketReports\25_07_2020\MatchedBets

Some other useful commands are:

cd : Change directory or display current directory path.
cls : Clear the window.
dir : Display list of contents of current directory.
help : Display list of commands or help about a command.
notepad : Run the Windows Notepad text editor.
type : Displays the contents of a text file.
assoc : Displays or modifies filename extension associations.
attrib : Displays or changes file attributes.
call : Calls one batch program file from another.
chkdsk : Checks a disk and displays a status report.
chkntfs : Displays or modifies the checking of disk at startup.
color : Sets the text and background colors.
comp : Compares the contents of two files or sets of files.
copy : Copies one or more files to another location. See also: xcopy
date : Displays or sets the computer's date. See also: time
del (or erase) : Deletes one or more files.
defrag : Defragment the specified storage device.
doskey : Display command history; define macros.
echo : Displays messages, or turns command echoing on/off.
edit : Runs the MS-DOS text editor. See also: notepad
exit : Closes the MS-DOS window.
fc : Compares two files or sets of files and displays the differences.
find : Searches for a text string in a file or files.
findstr : Search for a regular expression text string in a file or files.
goto : Used in a batch program file to jump to a particular line.
if : Used in a batch program file to perform conditional testing.
md (or mkdir) : Creates a directory.
more : Displays the contents of a file one screen at a time.
move : Moves one or more files from one directory to another directory.
rd (or rmdir) : Removes a directory.
rem : Used in a batch program file to identify comments.
ren (or rename) : Renames a file or files.
sort : Sorts input.
start : Starts a new window to run a specified program or command.
time : Displays or sets the computer's time. See also: date
tree : Graphically displays the directory structure of a drive or directory.
xcopy : Copies files and directory trees. See also: copy

The following internet related commands are not part of MS-DOS but can be typed at the MS-DOS command prompt:

arp : Displays ARP (address resolution protocol) translation tables.
ftp : FTP (file transfer program) to transfer files to/from server.
ipconfig : Displays internet configuration, including IP address.
netsh : Network services shell.
nslookup : Lookup IP address to/from domain name.
netstat : Displays current TCP/IP network connections and statistics.
ping : Ping the specified internet IP address or host name.
telnet : Starts a text-based telnet session to the specified host.
tftp : Transfers files to/from remote computer running TFTP service.
tracert : Traces the route to the specified IP address or host.

It's a shame people stopped learnig about DOS as soon as Windows 1.0 arrived, it's still really useful.

[Anbell]

If you check the box to "Export reports on market closure" you will know that a Log of your Automation activity, details of matched bets and profit and loss files are sent to the Market Reports folder as individual files per market and what a time consuming pain it is to manage those files one at a time.

You may also know that if you use DOS code ...

copy [folder location]\*.csv [folder location]\[combined file name].csv

... then DOS will combine all the single files in the folder into one larger file but you lose the race details and allocating the race details to the action/runner can be just as much a pain! But I just came across the following command line to be used in the appropriate Report folder.

findstr "^" *.csv >[Combined file name].csv

So to use you would go to (or preface the command with) the location of your Reports folder down to the [date]/[type of report] in the Command Prompt. This command will include the name of the individual files created by BA before each line so when you open the combined file in Excel the first column for each row is the file name. For example here is an extract from a merge on my Matched Bets reports of yesterday …


MergedReports.zip


Don't ask what I was doing, just experimenting with a theory! Anyway I hope this is of help to some.

I use a utility called TXTCollector which is really helpful

[firlandsfarm]

Stick all those commands in a file called AnythingYouWant.BAT When you double click it, it will execute the commands.
eg.
cd c:\Users\[user]\AppData\Roaming\Bet Angel\[name of user data folder for installation]\MarketReports\25_07_2020\MatchedBets
findstr "^" *.csv >[Combined file name].csv

Hi Shaun, yes that was my job for this morning … create a batch file. I thought of trying to see if I could do one where it requests the Date as a variable similar to using a variable search parameter in Access. Or maybe try and run the routine from within Excel but again need to check if Excel can action DOS commands or maybe try and build something in VBA.

Never thought of Task Scheduler, never used it before but yes … run it at say 01:00 every night would seem sensible. Now I know this can be done I think I will be using Practice Mode and Reports more often … just wish it could show who the winner was, that omission is a real drawback.

I agree your comment that people have forgotten DOS, I use it from time to time but am not proficient because of the irregular usage (I forget what I last searched for! ) but can usually find what I want with an Internet search. And I forgot about "SET", thanks for reminding me.

I use a utility called TXTCollector which is really helpful

Thanks for the tip-off Anbell. I will have a look and see what else it can do.

[ShaunWhite]

I thought of trying to see if I could do one where it requests the Date as a variable similar to using a variable search parameter in Access.

set /p myDate="Enter date: "
That will prompt and put your input into the enviroment variable 'myDate', validation is non existent obviously. You can then just use %myDate% or pass it to another batch eg

set /p myDate="Enter date: "
ArchiveMyStuff %myDate%
..and I think the becomes useable as the variable %1, a second parameter would be %2 etc

I've forgot it all myself now I like to put "pause" between each statement to start with or it flashes by.

You can run Excel macros via the prompt too I think. Bit of a pain to setup but 10mins saved everyday adds up over the months.

[firlandsfarm]

[set /p myDate="Enter date: "
That will prompt and put your input into the enviroment variable 'myDate', validation is non existent obviously. You can then just use %myDate% or pass it to another batch eg

set /p myDate="Enter date: "
ArchiveMyStuff %myDate%
..and I think the becomes useable as the variable %1, a second parameter would be %2 etc

I've forgot it all myself now I like to put "pause" between each statement to start with or it flashes by.

You can run Excel macros via the prompt too I think. Bit of a pain to setup but 10mins saved everyday adds up over the months.

Hi Shaun, thanks for your continued interest. I was researching before seeing your latest message and have come up with the following batch file …

Code: Select all

@ echo off

REM: Input Day reports required for
set /p DAY=What Date do you want Reports for?:

REM: Change folder to BA MatchedBets Reports folder
cd c:\Users\idc10\AppData\Roaming\Bet Angel\BetAngel_v1.55_0 - Inst01\MarketReports\%day%\MatchedBets

REM: Combine MatchedBets Reports into one file in the "Date" Folder
findstr "^" MatchedBetsReport*.csv >c:\Users\idc10\AppData\Roaming\"Bet Angel"\"BetAngel_v1.55_0 - Inst01"\MarketReports\%day%\CombinedMBReport_%Day%.csv

REM: Change folder to BA Logs Reports folder
cd c:\Users\idc10\AppData\Roaming\Bet Angel\BetAngel_v1.55_0 - Inst01\MarketReports\%day%\Logs

REM: Combine Log Reports into one file in the "Date" Folder
findstr "^" Log*.csv >c:\Users\idc10\AppData\Roaming\"Bet Angel"\"BetAngel_v1.55_0 - Inst01"\MarketReports\%day%\CombinedLogReport_%Day%.csv

REM: Change folder to BA ProfitAndLoss Reports folder
cd c:\Users\idc10\AppData\Roaming\Bet Angel\BetAngel_v1.55_0 - Inst01\MarketReports\%day%\ProfitAndLoss

REM: Combine ProfitAndLoss Reports into one file in the "Date" Folder
findstr "^" ProfitLossReport*.csv >c:\Users\idc10\AppData\Roaming\"Bet Angel"\"BetAngel_v1.55_0 - Inst01"\MarketReports\%day%\CombinedPLReport_%Day%.csv

REM: Assign location of Excel.exe
set excel_program=C:\Program Files (x86)\Microsoft Office\root\Office16

REM: Assign location of Combined Reports files
set csv_files=C:\Users\idc10\AppData\Roaming\Bet Angel\BetAngel_v1.55_0 - Inst01\MarketReports\%Day%

REM: Change working folder to Reports' Date folder
cd "%csv_files%"

REM: Open Reports' files in Excel
start "%excel_program%" "CombinedMBReport_%Day%.csv"
start "%excel_program%" "CombinedLogReport_%Day%.csv" 
start "%excel_program%" "CombinedPLReport_%Day%.csv"

I have intentionally given a REM statement for each action to explain what it does and how it does it so that others can tweak it for their own computer setup but simply put it asks for the Day you want the Reports combined for and puts the 3 resulting combined files (Matched Bets, Logs and Profit and Loss) into the "Date" folder in the Market Reports folder. It then opens the three files in Excel ready for inspection and processing.

I hope it works for others (this is the first time I have ever published a bit of code! ). If any more experienced coders have any enhancements to make I would love to hear from you.

[robsmith]

This is brilliant. It's going to save me a lot of time. Thanks guys.

[PerthO]

Hi All

If you want to get a snapshot of your results for the day, I have written a script which will extract the data from the ProfitandLoss folder.
It involves a few steps to install perl, but that is not tricky and if anyone is interested I can post how.
When installed it looks like this :

results.png

You can also run, for example, "perl get_results.pl -1" to get the previous day's results.

Maybe this can save someone some time. It was doing my head in trying to calculate my P&L in practice mode on the fly as there did not seem to be an easy way of cutting and pasting the column in question from the main Guardian display page.

This is taking the lowest figure from the 3rd column in each file from the ProfitAndLoss files, so would probably only suit pure trading, not straight backing, laying or back to lay.

This script is the basis of another script which keeps a running total of your balance in practice mode which can be loaded into rules files and shown as a stored value.
The speed of balance refresh is limited by how often Guardian writes to the Export Files, which I don't seem to be able to change (even with a manual export as soon as I have closed all trades)

Graham

[rhysc]

Hi All

If you want to get a snapshot of your results for the day, I have written a script which will extract the data from the ProfitandLoss folder.
It involves a few steps to install perl, but that is not tricky and if anyone is interested I can post how.
When installed it looks like this :

results.png

You can also run, for example, "perl get_results.pl -1" to get the previous day's results.

Maybe this can save someone some time. It was doing my head in trying to calculate my P&L in practice mode on the fly as there did not seem to be an easy way of cutting and pasting the column in question from the main Guardian display page.

This is taking the lowest figure from the 3rd column in each file from the ProfitAndLoss files, so would probably only suit pure trading, not straight backing, laying or back to lay.

This script is the basis of another script which keeps a running total of your balance in practice mode which can be loaded into rules files and shown as a stored value.
The speed of balance refresh is limited by how often Guardian writes to the Export Files, which I don't seem to be able to change (even with a manual export as soon as I have closed all trades)

Graham

Hi Graham, great work. This script would be very helpful & time saving if you don't mind sharing?

[ipsero]

This is an Excel workbook I use to view Market Reports for the day, mostly for horse racing but it should work for other markets.
You can import the P&L reports to obtain the winners name and profit/loss, and export this list to another workbook if you wish.
By clicking on one of the entries in the list you can view the log file for that event.
Any constructive feedback, suggestions etc would be welcome.