In-Play Interface / Progress Bar

Samples developed by Cymatic and others. Please share your spreadsheets here.
Forum rules
Users can post their spreadsheets here, to share with others and for discussion purposes.
If you download a spreadsheet from here, please carefully test any strategies yourself before using it with real money. Cymatic accepts no liability for any spreadsheets in this forum.
Now bring on the robots! Happy automated trading... :)

In-Play Interface / Progress Bar

Postby Gavin » Mon Sep 28, 2015 9:30 am

Some people have asked me to create an in-play progress bar indicator, or some sort of in-play trader interface, in Cymatic. This is something that I hope to do eventually, but it is unlikely to be very soon.

So I just thought I'd explain how you can probably do it yourselves in Excel!

The HorseFrontRunner sample spreadsheet has a sheet called Info, which shows you how to calculate the predicted race duration:

http://www.cymatic.co.uk/forum/topic672-100.html

You can then simply compare the predicted race duration with the actual race duration (from the countdown timer), to calculate the % of the race that has been completed.

The % completed can then either be displayed in Excel as a simple %, or use a progress bar (such as a GrooveProgressBar), or a slider, to display it visually. These visual controls can be found in the Excel ribbon within the section Developer | Insert | Active X Controls | More Controls.

If anyone has a go at it and feels like sharing the outcome, please post it here, it would be interesting to see.

Best wishes,

Gavin
User avatar
Gavin
Site Admin
 
Posts: 3832
Joined: Wed Jan 23, 2013 9:11 pm
Location: United Kingdom

Re: In-Play Interface / Progress Bar

Postby adenlark » Mon Sep 28, 2015 2:03 pm

Hi Gavin

Over the last few weeks, I've been experimenting with a graph that emulates some features from the In-Running/In-Play interfaces available in Geeks Toy and Bet Devil. Most Cymatic cells have been hidden, as I place bets via the grid interface. I've used a logarithmic bar graph, as it better shows odds movements at low prices. There is a compromise however, as log charts cannot handle 0 values, and will generate a harmless but annoying pop-up error message: "Negative or zero values cannot be plotted correctly on log charts. Only positive values can be interpreted on a logarithmic scale". Google says that some VBA code to disable error messages might work (didn't for me), so I have altered the formula in the helper column BZ, to return 1001 where odds of 0 exist. If anyone can find a more correct or elegant solution, please share!

Cells BL2 and BM2 have a basic numeric race timer (Will try the Active-X progress bar later), which uses a worksheet and formulae kindly shared by Nig299 and Canuk: http://www.cymatic.co.uk/forum/topic794.html#p3727 Cells BL3 and BM3 display the current time from B4

This is a rough edit, and the race timer and lookup formula need improvement, but it works - mostly. Have fun and happy trading!

CyNagsGraph-v1.xlsm
You do not have the required permissions to view the files attached to this post.
Wealth can neither be created nor destroyed. It is simply transferred from one illusion to another. - some guy on the internet
adenlark
 
Posts: 83
Joined: Sun Aug 03, 2014 10:00 pm

Re: In-Play Interface / Progress Bar

Postby Gavin » Mon Sep 28, 2015 4:06 pm

Hi adenlark,

That is really nice work, the graph works really nicely. Thank you!

Setting zero values to 1000 seems to be a decent solution to avoid the logarithmic charting restrictions causing an error.

In order to add the progress bar, you'll probably want to first do some examination of the B4 contents, in order to convert it to second even when it contains the ? and/or the minus sign. Minus sign values could be regarded at 0 progress. Values containing ? mean that the market had not been selected for viewing until after the race went in-play (so the precise time that it switched to in-play was not recorded). So when there is a ? it means that the value is just an estimate based on the scheduled start time.

I think the was a sample spreadsheet already loaded into the forum, that contains a formula for converting the countdown clock to an integer value, but I'm not sure whether the designer took account of the possible minus sign and ? symbol. There also exists the possibility of the value including the number of days, for example if it doesn't start for 1 day, then the value will be 1.00:00:00.

I would suggest doing something like (pseudo-code):
=IF(ISNUMBER(SEARCH("?",B4)), get the estimated time in play by simply comparing the scheduled time to NOW(), if B4 includes "-" or "." then 0 otherwise extract the actual duration in play from B4).

The presence of "." indicates that the race doesn't start for at least one day.

Excel's SEARCH is not case sensitive, FIND is case sensitive, so you could use either in this case.

Kind regards,

Gavin
User avatar
Gavin
Site Admin
 
Posts: 3832
Joined: Wed Jan 23, 2013 9:11 pm
Location: United Kingdom

Re: In-Play Interface / Progress Bar

Postby adenlark » Mon Sep 28, 2015 10:31 pm

Hi Gavin

While Googling for help on the Excel Progress Bar you suggested, I came across a post explaining how to use conditional formatting and a few merged cells to achieve a similar result. It's not pretty, but seems to do the trick.

Minor update:
CyNagsGraph-v1.1.xlsm
You do not have the required permissions to view the files attached to this post.
Wealth can neither be created nor destroyed. It is simply transferred from one illusion to another. - some guy on the internet
adenlark
 
Posts: 83
Joined: Sun Aug 03, 2014 10:00 pm

Re: In-Play Interface / Progress Bar

Postby Gavin » Mon Sep 28, 2015 10:58 pm

Hi Adenlark,

It looks really good. Nice find! I didn't know that was possible.

Kind regards,

Gavin
User avatar
Gavin
Site Admin
 
Posts: 3832
Joined: Wed Jan 23, 2013 9:11 pm
Location: United Kingdom

Re: In-Play Interface / Progress Bar

Postby Angelo » Tue Sep 29, 2015 2:30 am

Gavin, Thank You for the initiative.

Adenlark, Thank You for the commendable work done.

Kudos to both of you. :mrgreen:
Angelo
 
Posts: 139
Joined: Mon Sep 28, 2015 5:41 am

Re: In-Play Interface / Progress Bar

Postby xplayer » Tue Sep 29, 2015 3:28 am

Angelo wrote:Gavin, Thank You for the initiative.

Adenlark, Thank You for the commendable work done.

Kudos to both of you. :mrgreen:


I just want to second the above.

Well done and thanks for sharing.

Ray 8-)
xplayer
 
Posts: 89
Joined: Thu Aug 20, 2015 10:35 am

Re: In-Play Interface / Progress Bar

Postby Robin » Tue Sep 29, 2015 6:25 am

Really useful - many thanks.
Robin
 
Posts: 20
Joined: Sat Apr 04, 2015 7:24 pm

Re: In-Play Interface / Progress Bar

Postby adenlark » Tue Sep 29, 2015 3:19 pm

The lookup formula in the MinAvgMax worksheet sometimes returns #N/A. I've just just noticed that it returns #N/A for every race I've looked at this afternoon. Bollocks! The track abbreviation used in Cymatic A2 does not always match the abbreviation used in MinAvgMax column B, which explains the error. :oops: I did say it was a rough edit :roll: Changing the short track names in Column B seems to fix the problem.

Some abbreviations in MinAvgMax sheet changed, formula in BM3 changed to show Pre-Off when not in play, progress bar changed to show % of race remaining so that bar moves right to left (not sure I like this change yet).

CyNagsGraph-v1.2.xlsm
You do not have the required permissions to view the files attached to this post.
Wealth can neither be created nor destroyed. It is simply transferred from one illusion to another. - some guy on the internet
adenlark
 
Posts: 83
Joined: Sun Aug 03, 2014 10:00 pm

Re: In-Play Interface / Progress Bar

Postby xplayer » Tue Sep 29, 2015 5:30 pm

Hi adenlark,

Many thanks for this update.

Keep striving for perfection - I believe you will get there shortly.

Ray 8-)
xplayer
 
Posts: 89
Joined: Thu Aug 20, 2015 10:35 am

Next

Return to Excel - Sample Spreadsheets

Who is online

Users browsing this forum: No registered users and 0 guests

cron