bha ratings

Each week the BHA (British Horse-racing Authority) updates the last 6 performance ratings of 13,500 horses into a csv file/ google sheets (with each horse identified in columns for name etc plus columns for each of the last 6 performances).The data I wish to manipulate is within the 13,500 rows by 6 columns of ratings. A complicating factor is that the 6 data columns mix text (a letter of little interest) and a number (the key data) for each performance (e.g. T:88). I am able (laboriously) to split the text out into columns which I delete and to add a column to identify the best numerical performance (highest performance rating) for each horse. I am then able to add further columns to incorporate a calculation based on the relationship between the individual performances of certain of the horses. SO FAR SO GOOD I HAVE THE SPREADSHEET I WANT. Unfortunately a week later the BHA's new spreadsheet for the past week's 2000+ performance ratings will, inter alia, change all the existing reference points for 13,500 horses into different cells as well as adding new/ deleting old performance data (to give the latest 6 performances only). How, can I create a spreadsheet that manipulates the data as I want but can be updated weekly in line with the next BHA spreadsheet.


Let me know if you have any questions