Excel assistance required please!

[jonahjones]

I have no experience with Excel and so need some help please if anybody would be so kind. :)

My entire pro loser history (running to over 10,000 entries) is contained in a spreadsheet identical to Betfair's finest (attached).

This is fine from a record keeping point of view but I need an extra column to show the running total which would take into account commission at 5%.

Any ideas?

Many thanks in anticipation.

[mathare]

I'm on it...

[jonahjones]

I'm on it...

New I could rely on you Mat. :)

[mathare]

I saw Glos is looking on and had to get in there first :laugh

The thing with the pro losers (and many lay systems) is that you get multiple qualifiers in the same race. This complicates matters when one of these selections wins and the others lose because the commission applied by Betfair is on the total profit on the market (if any). You can't simply apply 5% commission to all losing horses and expect the sums to work out correctly.

This can be overcome with some VBA but it depends on what you're after and how accurate you want these numbers.

You have another slight problem with the formatting for column J in the attachment. Excel is currently treating this as text, not as number/currency format. That means when you sum the values in that column is treats (11.10) as a text string and not as -11.10 and so excludes it from the sum.

When you download from Excel is any number formatting preserved or does it all come across as text?

[jonahjones]

When you download from Excel is any number formatting preserved or does it all come across as text?

The first 3,000 or so entries are in the format as attached.

[mathare]

That's a super format, I can handle that. :)

Adding:
=ROUND(0.95*SUMIF(N$1:N1,"WON",O$1:O1),2)+SUMIF(N$1:N1,"LOST",O$1:O1)
into column Q on the second attachment will give you what you want for that lot with the problem still that this applies 5% commission to each and every winner, even though you have have had a loser on the market and thus a loss on the market overall and would pay no commission. You can only get round this with VBA...

I can explain that formula in detail if required, by the way

Now what about the other 7000 entries?

[jonahjones]

Mat,

I assume to put this function into column Q I should highlight the column and paste the function into the function bar?

I have done this and have produced the attached.

The other 7,000 or so are in the format of the first sheet and, therefore, recognised as text.

[mathare]

Select Q1, then use Edit-Fill-Down to populate the rest of the first 3000 or so cells in column C. That will give you a running total after each bet.

What that formula is doing is summing 0.95 * sum of column O to chosen row when N shows WON (ie applying %5 commission to all winning bets) and adding that to the sum of the losing bets from the start to the current row. The Round at the start just makes sure that the sum after commission is to 2decimal places so you don't get a total of £163.58537 or something similar

The other 7000 are going to be harder to deal with. Let me have a quick think about those....

[mathare]

OK, thinking time up. Betfair are sods - simple as that.

The exported data is in reverse chronological order which is just a pain in the :butthead: if you ask me. I want to be able to download data and add it to the bottom of the existing list, not have to insert it at the top. ;fire

Also as I said earlier their exporting function brings everything over in text format whereas you'd want to preserve the number formats etc.

VBA is your only way forward I'm afraid. You have a choice though:
1) a macro that takes the raw Betfair exported data and converts it to a more user friendly format and you then use the formula given earlier to maintain a running total with 5% commission applied to all winners
2) a macro that creates the same user friendly format but also settles lays accurately by settling on a per-market basis and applying commission as Betfair apply it

The second macro is more work than the first. Neither are quick and simple but both can be done fairly painlessly.

BTW By user friendly I mean in a format you define/choose - with certain limitations. Personally I have never liked their Market format and would split that into date, time and course while throwing away the rest of the data in that string (UK and race type)

I might have some time over the next couple of weeks to thrash this out but no promises

[jonahjones]

Mat,

You have been a tremendous help and thank you very much for your time.

I am very grateful.

I will play around with remaining entries to try and come up with a solution. If not I will enter them manually. :yikes:

It annoys me too when Betfair portray the most recent transaction at the top. ;fire

I have managed to overcome this by sorting in date order.

Naive question time! Do you think that if I use find "(" and replace with "-" for the other 7,000 entries it would make any difference?

I appreciate your kind offer to create a macro as a solution but feel I have already taken up enough of your time and don't want to impose further.

[mathare]

I will play around with remaining entries to try and come up with a solution. If not I will enter them manually. :yikes:
That will take a long time and isn't worth it. I can write a few lines of code that can convert textual numbers to proper numbers and it'll save you hours and eliminate potential typing errors


Naive question time! Do you think that if I use find "(" and replace with "-" for the other 7,000 entries it would make any difference?All it will do is give you text strings of -11.10 instead. It won't affect whether Excel will treat then as text or numeric values


I appreciate your kind offer to create a macro as a solution but feel I have already taken up enough of your time and don't want to impose further.It's no real hassle for me as long as you're prepared to wait short while for it.

Think about it - whatever you do to the data you have in the spreadsheet at the minute you will need to do on any further data downloads from Betfair. And why do boring, repetitive tasks yourself when a computer can do them for you? :)

[jonahjones]

It's no real hassle for me as long as you're prepared to wait short while for it.

Well, if you insist. :)

Thanks again.