Cleaning Data VBA Code

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
KJLP60
Posts: 24
Joined: Thu Jun 28, 2018 8:46 am

Hi All,

Thought I'd offer up some basic code for anyone who wants to or does Store and export SV's to analyse via excel. I put this together so I could combine the SV's folder using Powerpivot and analyse 000000's of rows in 1 go. Before Powerpivot couldnt recognise the data, because the SV's don't have their own header.

It took me a fair while to piece this together because I have no coding background, so I'll answer any questions if I can, but others will probably know a lot more about the code than me. It's a bit messy the way it embeds the call functions, but the code works and is quite fast (It should knock out 200 files whilst you make a coffee.)

A Few notes.
- To use the below, in the guardian Rules editor when exporting values, You need to specify export file format - each selection on it's own row.
- You'll need all three bit of code to run it the way I use it, or you can play around and do some other stuff with it.
- Read the notes in the code snippets below, I try to explain what each bit is for, so you can tailor it to suit your needs.
- Code requires some minor adjustment, as it will only reference a folder on the users system obviously.


I find this technique works better than using excel to data capture, Excel just bugs out after running for too long I find.

Anyway, just wanted to give back to the community as there's some nuggets of gold in this forum, and people seem only too willing to share. I hope this helps.

Cheers,



I use convert to XLSX so I can split the worksheet between Pre-race and In-Play. It also seems to make the files quite a bit smaller.

Code: Select all

Sub ConvertCSVToXlsx()
    
    Dim myfile As String
    Dim oldfname As String, newfname As String
    Dim workfile
    Dim folderName As String
    
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
'   Capture name of current file
    myfile = ActiveWorkbook.Name
    
'   Set folder name to work through - Click and copy the folder address at the top of the finder window and paste into the code. DO NOT 	 
'    FORGET TO BACKSPACE THE VERY END OF THE FOLDER PATH NAME . I personally copy and paste all raw data to a new folder and run the code ' 
'     there, in case I muck up the code in some way, I still have the raw data so I can try again. You cannot "undo" VBA.
    
    folderName = "C:\Users\XXXXX\"
    
'   Loop through all CSV filres in folder
    workfile = Dir(folderName & "*.CSV")
    Do While workfile <> ""
'       Open CSV file
        Workbooks.Open filename:=folderName & workfile
'       Capture name of old CSV file
        oldfname = ActiveWorkbook.FullName
'       Convert to XLSX
        newfname = folderName & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & ".xlsx"
        ActiveWorkbook.SaveAs filename:=newfname, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        ActiveWorkbook.Close
'       Delete old CSV file
        Kill oldfname
        Windows(myfile).Activate
        workfile = Dir()
    Loop
'        Loop Through Files will loop through and cleandata using the sub cleandata macro below.
   Call LoopThroughFiles
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

End Sub 
Here's the code the allows us to loop through all the files in the folder. This calls the third snippet of code whilst looping through the files in the folder. It will save and close each file as it works through it, but it ill happen in the background as screenupdating is turned off.

Code: Select all

Sub LoopThroughFiles()

    Application.ScreenUpdating = False

    
    folderName = "C:\Users\XXXX\"
    If Right(folderName, 1) <> Application.PathSeparator Then folderName = folderName & Application.PathSeparator
    Fname = Dir(folderName & "*.xlsx")

    'loop through the files
    Do While Len(Fname)

        With Workbooks.Open(folderName & Fname)

           ' here comes the code for the operations on every file the code finds
        Call Cleandata
        
        End With

        ' go to the next file in the folder
        Fname = Dir

    Loop
    
    Application.ScreenUpdating = True
    
    
    
End Sub
You may not need all of the below - But this is how many stored values im exporting. This puts the SV name as the heading, rather than in the column next to it. I can't work out why BA does this anyway!?

Code: Select all

Sub Cleandata()


    'Copy and Paste Single Cell
    Range("F2").Copy Range("G1")
    Range("H2").Copy Range("I1")
    Range("J2").Copy Range("K1")
    Range("L2").Copy Range("M1")
    Range("N2").Copy Range("O1")
    Range("P2").Copy Range("Q1")
    Range("R2").Copy Range("S1")
    Range("T2").Copy Range("U1")
    Range("V2").Copy Range("W1")
    Range("X2").Copy Range("Y1")
    Range("Z2").Copy Range("AA1")
    Range("AB2").Copy Range("AC1")
    Range("AD2").Copy Range("AE1")
    Range("AF2").Copy Range("AG1")
    Range("AH2").Copy Range("AI1")
    Range("AJ2").Copy Range("AK1")
    Range("AL2").Copy Range("AM1")
    Range("AN2").Copy Range("AO1")
    Range("AP2").Copy Range("AQ1")
    Range("AR2").Copy Range("AS1")
    Range("AT2").Copy Range("AU1")
    Range("AV2").Copy Range("AW1")
    Range("AX2").Copy Range("AY1")
    Range("AZ2").Copy Range("BA1")
    Range("BB2").Copy Range("BC1")
    Range("BD2").Copy Range("BE1")
    Range("BF2").Copy Range("BG1")
    Range("BH2").Copy Range("BI1")
    
    'Delete Columns
    
    ActiveSheet.Range("$C:$C,$E:$E,$F:$F,$H:$H,$J:$J,$L:$L,$N:$N,$P:$P,$R:$R,$T:$T,$V:$V,$X:$X,$Z:$Z,$AB:$AB,$AD:$AD,$AF:$AF,$AH:$AH,$AJ:$AJ,$AL:$AL,$AN:$AN,$AP:$AP,$AR:$AR,$AT:$AT,$AV:$AV,$AX:$AX,$AZ:$AZ,$BB:$BB,$BD:$BD,$BF:$BF,$BH:$BH,$BJ:$BJ").EntireColumn.Delete

    'Set Selection name
    Range("C1").Value = "Selection"
    
'    This Just sorts by Selection in numerical order    
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Sort key1:=Range("C1", Range("C1").End(xlDown)), _
    order1:=xlAscending, Header:=xlYes
    
'    NOTE: The next two code snippets (Including Delete Data) was for my purposes, I wanted to sort pre-race from In-play data on different worksheets in the same file. I did this by filtering the SP Column to 0, then copying and pasting that to the Pre-Race worksheet, then deleting that from the In-Play worksheet (Which had the original data, it was just renamed), keeping all the In-play data in that worksheet. I then unfilter.
 
'   You will need to change the Autofilter field to filter the SP Column

    Sheets(1).Name = "inplay"
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets(2).Name = "Pre-Race"
    Sheets("inplay").Select
    ActiveSheet.Range("A1").AutoFilter Field:=14????, Criteria1:=0
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("pre-race").Select
    ActiveSheet.Range("A1").Select
    ActiveSheet.Paste
    Columns("A:Z").AutoFit
    
    'Delete Data
    
    Sheets("inplay").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.EntireRow.Delete
    ActiveSheet.AutoFilterMode = False
    Columns("A:Z").AutoFit
    
    'Sort By SP - You probably don't need this but if you do - NOTE You will need to enter the correct new column to sort by, based on how many Stored Values you capture and where the SP column is
    
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Sort key1:=Range("??", Range("??").End(xlDown)), _
    order1:=xlAscending, Header:=xlYes
    
    ActiveWorkbook.Close SaveChanges:=True
    

End Sub
User avatar
ChrisF
Posts: 36
Joined: Mon May 22, 2017 10:48 am

Due to my laziness and having a strategy that worked I have ignored most of the new stuff that came in different versions, so can you please tell me how to store SVs for this code, is it the store as a history list, if so where is the default folder where these files are stored?
KJLP60
Posts: 24
Joined: Thu Jun 28, 2018 8:46 am

You just need to export the values however you see fit, to the folder of your choosing.

There's options for which values export, such as Shared only, Not shared only, History Lists or Not etc.

You also choose the folder in which you want to export the values to, I send to a dropbox folder because they start to really add up in size.

Also worth mentioning that this didn't work great on a VPS, it often skipped 3 or 4 seconds of data when I was after 1 second data, works fine on a good laptop though.
User avatar
ShaunWhite
Posts: 7038
Joined: Sat Sep 03, 2016 3:42 am

KJLP60 wrote:
Tue Nov 23, 2021 12:26 am
Also worth mentioning that this didn't work great on a VPS, it often skipped 3 or 4 seconds of data when I was after 1 second data, works fine on a good laptop though.
What vps are you using? Anything decent will perform way better than a laptop. Or did you just run it for longer, because sometimes these things start ok then just get slower and slower.
KJLP60
Posts: 24
Joined: Thu Jun 28, 2018 8:46 am

I was running a Vultr VPS with 4 virtual cores and 8 gig with an ssd. The laptop has a Ryzen 4800u with 16 gig which would surely outperform the VPS?
User avatar
ChrisF
Posts: 36
Joined: Mon May 22, 2017 10:48 am

Sorry for been the dimwit in the class but I have no idea what this meas "You just need to export the values however you see fit, to the folder of your choosing."

I'm logging all SVs to the log files, within betangel I don't see a command to export these SVs. I have no idea how to get the data in the correct format. Please explain the process as if your teaching a toddler to do something.
User avatar
ChrisF
Posts: 36
Joined: Mon May 22, 2017 10:48 am

Any help on my query, I've search betangel software high and low for a way to export these stored values. I have a log file full of them but have no idea how to export them as suggest above, please give me a clue.
User avatar
ChrisF
Posts: 36
Joined: Mon May 22, 2017 10:48 am

Betangel support gave me the answer to my question

There is a rule type to do this called "Export All Stored Values" This automation rule will export all Stored Values at that point in time

Cheers
KJLP60
Posts: 24
Joined: Thu Jun 28, 2018 8:46 am

Sorry for the delay in getting back to you Chris.

Glad you found an answer to the question.

I'm tipping there's some gremlins in changing the code for your needs, so let me know if any issues.

Cheers,
Wolf1877
Posts: 354
Joined: Fri Sep 08, 2017 10:59 am

KJLP60 wrote:
Wed Nov 24, 2021 10:32 am
I was running a Vultr VPS with 4 virtual cores and 8 gig with an ssd. The laptop has a Ryzen 4800u with 16 gig which would surely outperform the VPS?
I tend to agree that any decent laptop or desktop would surely outperform a vps on processing. You'd surely have to pay £100s per month for a VPS that outperforms a fairly average medium spec dedicated PC. Where the VPS would probably win is on latency which is great for low power processing that can benefit from the faster comms.
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”