Freelancer

Description

This project is for the production of two macros to work within Excel 2000 to convert data downloaded from the Racing Post website (www.racingpost.co.uk) via the Excel new web query function to a form suitable for transfer to an existing database.

The first relates to the distances by which horses were beaten by the race winner. The Post lists the losing horses in order, and the distance each finished behind the one in front. Distance is expressed in three ways - whole numbers, mixed numbers (but only using three fractions - quarter, half and three quarters) and text (three terms: "shd", "hd" and "neck", which are abbreviations for short head, head and neck, respectively).

A typical situation is shown in col. 1 of book1.xls. In this race 17 horses finished behind the winner, two by distances expressed in whole numbers, seven by distances expressed by mixed numbers and eight by distances expressed by text. Using conversion rates "shd" = 0, "hd" = 0.1 and "neck" = 0.25, what is wanted is a macro which when applied to col. 1 will produce what is shown in col. 3, ie will convert the mixed numbers and text distances to decimals (two places), and then generate a total for each horse representing the distance it finished behind the winner, expressed negatively. Thus in the example the last but one horse finished 8.1 behind the winner - the sum of the first 16 cells in col. 2 - and is shown as -8.1 (rounded to one decimal place).

The second macro relates to the weight the horses carried. Col. 6 of book1.xls shows a typical download. What is wanted is a macro which will ignore any suffixes such as "b1", "v", "extraweight3" etc, and convert the weight to the format shown in col. 7. Thus the Post's 9-4 becomes 9.04 (9.04 merely being a different way of expressing 9 stone 4lb, NOT a decimal number). Although not included in col. 6, the macro needs to be able to convert weights up to 13 stone, ie what the Post would record as 13-0 converted to 13.00.


Additional files submitted:
Book1.xls