Problem with Horse Form

[ians1711]

Only the first day but I had some strange results when I tried to log the Form of certain horses yesterday. Any ideas? Do the numbers ring any bells with anybody?

1. When I log to Excel I select Saddlecloth number and form.
2. I then have VB code to check the form and log it to another part of the spreadsheet if it looks interesting.
3. Out of 249 horses logged, only 5 had suspicious values:

Epsom 18:00 Markhesa, recorded 476552, should have been 10-3204
Epsom 20:10 Mausin, recorded 1663042, should have been 4-6453
Goodwood 16:00 Ships Biscuit, recorded 40329, should have been 5-31
Goodwood 17:10 Rakaan, recorded 40909, should have been 01-2012
Stratford 17:15 Dan Maguire, recorded 54236-6, should have been 1P-5936

Apart from the last one which had a dash in it, three of the others weren't even valid form numbers as they had 5 or 7 numbers - should only be 6.

Puzzling
Ian S

[osknows]

Excel is seeing some of the form values as valid dates and converting to date serial numbers. It often happens with both form and weight fields.

Try formatting the affected columns in Excel as Text. It won't convert existing dates back to text but it will prevent any future data being converted to dates

[ians1711]

Osknows - thanks for that. Looks like when Gary exports the data he outputs them with a leading ' to force them to be treated as text, so does your theory still hold?

Ian

[GaryRussell]

Were other form values from the same races as the wrong values correct?

[ians1711]

Yes - most of the races had 5 or more selections recorded. All except the one noted were correct when compared with racingpost, sportinglife and, most importantly, timeform - which is where I think betfair gets it's form data???

Ian

[GaryRussell]

Yes, they get it from Timeform.

Osknows is correct though. I checked the first two and they have definitely been interpreted as dates. If you format the column as date then enter 10-3204 and change the format to number it displays 476552 which is not a coincidence. It does output ' in front of the value so not sure how it got interpreted as a date. When you record the value is it being referred to by a different cell in a formula first or are you taking it directly? Also are you doing this with VBA? If so could you post the code so we can have a look?

[ians1711]

I'll have to post the code when I get home this evening. As far as I can remember I just copy from the cell you provide the value in to the logging cell with code like Cells(x,y).Value = Cells(a,b).Value

[osknows]

Once the data is in excel the apostrophe identifies the formatting. Your code then writes the cell value (not the formatting identifier ') to a new cell and excel interprets it as a valid date

As a test, in a blank workbook in Sheet1 type '11-10 in cell A1

Then run this
Sub test()
With ThisWorkbook.Sheets(1)

Cells(1, 2).Value = Cells(1, 1).Value

End With
End Sub


What you see in cell B1 is 10-Nov as date or 40492 as serial

You can get around this by
a) formatting cell B1 as text
b) change Cells(1, 2).Value = "'" & Cells(1, 1).Value

[ians1711]

Excellent - thanks for that