How to create a single sheet with all the runners and data?

[Laugro1968]

Hi,

Sorry if the topic has already been discussed, but I didn't find it.
How do I create a "dashboard sheet" with the event, start time, runner name, back and lay odds of every runner on a single sheet (containing a listing of all the runner of the day)?

Thank you,

Laurent

[EyePeaSea]

Are you talking about a dashboard that shows historic data or just a snapshot of the current data? I don't use any of the graphing/tracking functionality of BA - but from what I understand, it can give you an incredibly good real-time view of what's going on in a market. And there are lots of very good and helpful users on here who can help.

Anyway - if you can't get what you want from within BA, then you could do this with VBA in your Guardian spreadsheet.

Using the 'Worksheet_Change' function on each worksheet, call a routine that runs down the current (just changed) worksheet and copies the cells to the dashboard sheet. Each time it goes to copy the data, it would need to scan down the dashboard sheet to see if that Event/Runner already existed; if it does exist, amend that line. If it doesn't exist, just add a line at the bottom.

You'd have to put some extra logic in there, to remove or mark runners on the dashboard if they disappeared from the Bet Angel (x) sheet.

It's not hugely complex, although you may run into problems if you're trading at the same time using that worksheet - if your code runs too slowly then BA may get grumpy if you're in the middle of a trade and then spend 5 seconds locked up whilst you update the dashboard.

If you provide an example in Excel of what you'd want the dashboard to look like, then I'll see if I can knock up a proof of concept for you. The only problem may be my DIY project - I'm turning my garage into a home office and it may well kill me (9 hours yesterday sawing, hammering, drilling and bleeding). In which case, I won't be able to give you the example code.

[EyePeaSea]

Here you go... Let me know if it's what you're after.

[Laugro1968]

Hi EyePeaSee,

Yes this is what I'm after.
I currently have my own spreadsheet attached below, but I don't know how to start with the next list of runners once the name cell of the previous event returns empty. I currently need to filter, sort and then delete all empty cells from the dashboard. The purpose is to add data extracted from various sources to find the selections I'm after. I'll try your spreadsheet later on.

Thank you for your support so far!
Best regards,
Laurent

[Laugro1968]

Hi EyePeaSee,

This is an impressive piece of work and way more advanced than mine!
I take it that you're using VBA programming?

Best,
Laurent

[EyePeaSea]

I take it that you're using VBA programming?

Hi Laurent,

Thanks - yes, it's VBA. It's just an example - and with lots of comments in the code so hopefully it won't be difficult to follow. If it's roughly what you're after, then let me know if you need help modifying it.

[Laugro1968]

If it's roughly what you're after, then let me know if you need help modifying it.

Hi EyePeaSea,

Yes, it is. Please allow me some time to finalise exactly the relevant data which I'm after (from betfair and other exportable or scrapable sources), and go through your code to get acquainted with vba. I'll then come back to you.

Cheers,
Laurent.

[Laugro1968]

Hi EyePeaSea,

When I connect your workbook to BA, the first BA sheet connects at row 61 instead of row 4 as, I suppose, would be more logical.
How should I proceed to add more BA sheets?
I'm looking to have a full listing of runners on the dashboard each morning and add/collate columns of data for each runner obtained mostly from Proform Racing.

Thank you,
Laurent

[Laugro1968]

When I connect your workbook to BA, the first BA sheet connects at row 61 instead of row 4 as, I suppose, would be more logical.
How should I proceed to add more BA sheets?

Sorry, just found out how to manage it!
Laurent

[Laugro1968]

Hi Ian,

I hereunder attach an example dashboard. Indeed, I export (static) ProForm data and match the runners against the live (dynamic) dashboard data. This enables me to look for over- or underrated runners.
At this stage, I don't need the spreadsheet to be constantly connected to BA, a regular snapshot being sufficient.
Column K matches the runners names. This has to be checked manually at each snapshot because none runners disappear from the dashboard but not from the static data, while sometimes, it's just the spelling which differs.
Thanks to your spreadsheet I'm very close to what I'm trying to achieve. It would be great if you could eliminate the value changes which shows in brackets, as it affects the odds difference calculation in column L.

Thank you and best regards,
Laurent

[nigelk]

Re The spelling mistakes.

Looks like the problem arises when the names in col AW include an apostrophy. The formula in col AX removes these and I've changed the formula in col K to point to AX, all are reporting as ok.

It might be helpful to clean the data as much as possible after importing it. ie, check for and remove any leading/trailing spaces, and check all characters are alphabetical

Cheers,Nigel

[Laugro1968]

Thank you Nigel,

Will have a look and feedback tomorrow.

All the best,
Laurent

[Laugro1968]

Hi Nigel, Ian,

Thank you, the fomula works like a charm to match the runners now and manually eliminate non runners in the course of the day.
Regarding your spreadsheet Ian, can I ask you how to make the following adjustments
1. Having just one column headers in row 1?
2. Just have the lay - back values, without the delta calculation (only the back and lay figures)?
3. Delete empty column J (between "Low Odds" and "Last Update")?

Example below.

I ould appreciate it,
Cheers
Laurent

[Laugro1968]

Hi all,

Went through the code and it's all done. Nice and neet!

Laurent