Bet Angel and Excel

[offthelip]

Hi Cperry

@ Offthelip
The sheet you attached in your previous message works fine when you connect from cell A1 but the cells are set to the original arrangement of the InfoFields so instead of occupying cells B3:K3 (C2:C6 and F2:F4), everything else links and looks clear.

I don't understand what you are referring to here, I haven't attached any sheet for ages. Which workbook are you talking about?

[CPerry]

Apologies, the last message I was referring to was from SamT. That's what I get for typing up a quick reply.

[SamT]

By definition of "average" this will always be zero.

Makes sense,

here is a EXCEL function for this ( 4 of them actually) change the equation in F14 to:

What did you get for SD?
I got 4.5us - 0.045ms
I bet I did it wrong

[offthelip]

I get:
0.002457

equation in F14 is:
=STDEV(G11,G65526)

[SamT]

Attached is The Bet Angel Sheet Code and the Micro-timer UDO, (Class Module,) which will be used all over this project

I tested it pretty good, but you will put it to the acid test. It will record every change the Bet Angel Program makes to Excel's Bet Angel Sheet. This will tell us the order BA updates the cllse and give us accurate enough timing data.

Manualy copy the Worksheet Code to the Bet Angel Worksheet, then Drag the MicroTimer Class Module to the Bet Angel Project's ThisWorkbook Label. Run it just like the previous timing tests.

I did add NOW() and MicroTimer Start times to Row two of the Log sheet.

[SamT]

The sheet you attached in your previous message works fine when you connect from cell A1 but the cells are set to the original arrangement of the InfoFields so instead of occupying cells B3:K3 (C2:C6 and F2:F4), everything else links and looks clear.

Does that mean that BA won't let you put the Info Cells like I want? (All in a Row.)

[CPerry]

Awesome! The British races start at 12:15 tomorrow so I will run it then and report back a saved copy at the end of a few races. I'll do a couple this time now we're getting a little closer just to be sure!

Yes, BA always puts the InfoFields in that specific order (probably getting directions from BA directly in some capacity but that information is no where to be seen in their standard documentation)

[SamT]

Ah, well. Just some more collating to do.

[CPerry]

Race 1 20-16 Mins.xlsRace 1 10-7 Mins.xlsRace 2 20-16 Mins.xlsRace 2 10-7 Mins.xlsRace 2 3-0 Mins.xls

Good afternoon gents,

Please find attached 5 different tests using that code. I had my second laptop playing a YouTube video whilst the trading laptop recorded: 'Race 1 10-7 Mins' and 'Race 2 20-17 Mins' just to see whether systems fighting over internet connection affected the results massively. Each test seemed to consistently take around 3.5 minutes or so.

I hope this is correct and what we are hoping for.

Cheers,
CPerry.

[SamT]

Excellent:
Here is one of those, truncated to 2 seconds of play, with some interesting analysis.

[offthelip]

Hi All,
Excellent recordings and analysis.
There was one extra item I put in which is the minimum time between batches ( Min(f10:f101) this was 0.066. I assume the units are seconds.
The way I interpret this is that we have 66 ms to do all the EMAs, RSI and trigger calculations and write the results back before we get into the "overrun" situation. That should be enough by my estimation.
All systems go!!

By the way I have just noticed that we have now become the longest thread on this forum and the EXCEL forum, do we get a prize for that??

[SamT]

Here ya go...

Till you

the minimum time between batches (Min(f10:f101) this was 0.066. I assume the units are seconds.
The way I interpret this is that we have 66 ms to do all the EMAs, RSI and trigger calculations and write the results back before we get into the "overrun" situation.

I got the same 66ms. All the BA Excel is going to do is place one array (A1:AF & LastRow) into the Output Object to be read and parsed by the Helper Excel DataParser Object.

Maybe Excel is fast enough to do it all in one Workbook? What do you think? Is it worthwhile writing it that way first? It would be a bit simpler.

BTW, the original Log had 10000 records. Did you check the minimum of that log? Or do you think ~100 samples is enough? I didn't 'cuz I'm lazy and didn't feel like writing a sub to Paste an Array of Cell Formulas each 7? Rows. I made that sheet by hand.

[offthelip]

Each EMA takes about 5 arithmetic/ logic calculations
MACD need 3 EMAs plus a few more calculations , RSI need 2 EMA plus a bit of calculation
So about 25 calculations for the emas, double it to take account of the MACD and RSI calculations
So about 50 calculations per brand per update. With 4 horses that only makes about 200 calculations all done directly in memory.
I have just run a test looping round the EMA calculation 10000 times using the microtimer, my computer did it in 1.7ms
So I think it should easily fit in one workbook, just using whatever multithreading EXCEL can manage.
However I think writing out the array will take the time:
I wrote out an array 100 by 5000 rows and it took 0.3 seconds:
Writing out a smaller array one row by 100 columns only took 1ms.

So I would arrange the layout of the worksheet to allow just writing a single row or single column of data each iteration.
I haven’t looked carefully at the layout of Custom settings workbook, but with a quick look at layout on the Control panel, I am not sure what this is supposed to be doing, is this where the output is going to be written? because if it is it will need changing

[SamT]

How did you write the array? With a loop,or by an equivalency?
There are only three Ranges that need to be Read, if I understand the OP. The Row count is limited to 30 by 5, 11, and 12 columns. The entire possible used Range of the Bet Angel sheet is only 71x36

The information, or Financial. section of the Control Panel is a direct copy, maybe rearranged. I think that a simple equivalency with the right cell on the Bet Angel sheet should work. That is an Excel Calculation Event.

A single Brand's data is two Rows by 71 Columns, or 2Rx11C and 2R12C. There can be 30 Brands

As currently imagined, each Brand UDO can write it's own results to the Control Panel, or they can all send their results to a Data Accumulator/Sheet Writer. At one or two Brands, it might be faster to have them write to the sheet. At n Brands, it might be faster to use the Accumulator UDO.

[offthelip]

I am writing the array by assigning a previously defined and randomly populated variant array to it::

Range(Cells(1, 1), Cells(lastrow2, lastcol2)) = var2

Also I have modified the WIP custom settings/ control panel sheet to the layout which I think will work best.
Two things firstly setting it out so that outputs can be written out on a single line.
Secondly getting the correct columns laid out for what Cperry asked for which was MACD and RSI.
I also thought that record the time and price with each update line is going to make debugging much easier, and is very useful in the live situation when trying to develop the strategy.

[SamT]

Since we now have a decent MicroTimer, I was going to make the next to last version log times and activities for the Entire project. Might use Compiler Constants so the code itself would not have to be rewritten just to re-log that after it's in production and we need to find a bottleneck.

The Control Panel Sheet is still in a state of flux awaiting CPerry's final input. I think of it more as a Dashboard/Display Panel with a few Command buttons so the User can manually control each Brands BA Status.

@ CPerry, We still must needs to Name every possible Bet Angel Data Point, (or Input Field,) for every possible Market type that BA supports. Set up A Bet Angel sheet with all possible data inputs for each type of Market/Betting Event. We only need 1 or 2 Brands in each book. Then Run the MicroTime logger for 3 cycles. Save that Workbook named by Market Type,(ex: New Jersey Cattle Exchange.xls, BaseBall Games.xls.)

Go thru the Bet Angel sheets in the workbooks and name every data field in a style like the BA Sheet in the attached.

[CPerry]

Custom Settings Sheet - CPerry V3.xlsTottenham Hotspur Correct Score Market.xlsMan City vs Arsenal Match Odds Market.xlsb

Firstly, I have updated what I would like to be the main spreadsheet with a few minor tweaks (Offthellip has kindly added in the additional variables for the indicators that I will be running with Version 1 of the finished project). At this point I would just like to ask, do the trigger points i.e. MACD 30/70 levels need to be included in the control panel or will this go directly into the coding? I ask because these will be variables I play around with when testing, I would simply like them to be in the most efficient place possible and to know where they are to experiment with.

Next, I have been reading into market behaviour and have come up with some additional safety measures I think you will both agree are important to include.

Self-note: I feel this is slightly jumping the gun and wont go into the project just yet but I thought I’d type it up now to reference later with proper coding terminology:
1) When markets go berserk and fluctuate irrationally (only in the most extreme of instances does this concern). Do not close from a back order if price is now greater than 5 from back order, do not close from a lay order if price is now less than 5 from lay order.
2) When only partial orders get matched or when the price moves and my order doesn’t get matched, see and elaborate on small notepad sketch.

Now in response to your previous message. I now believe the control panel includes all of the info fields that need to be accounted for. I may be being really stupid here but what do you mean name every possible BA data point for every possible race market? The titles previously typed up include everything, don't they? Brand 1 accounts for whatever may be in that particular cell, whether it be a favourite horse, the 0-0 scoreline or a name of a country in an international competition. I have used today’s premier league football matches as an example, please see attached files. I have included the 2:15 and 4:30 matched, Tottenham was the correct score market so there were loads of brands and the Man City match was the Match Odds market so there was only 3; Man City, The Draw or Arsenal (cycled 3 times at different intervals - identified via the sheet tab names. In any case, brand 1 would be whatever is in that first row so what do you mean by every single data point? Again, probably just me being really thick...

Hope you've both had a good weekend, as ever looking forward to your next message(s).

Cheers,
CPerry.

[SamT]

When you use Bet Angel in the Stock market, do you still use Back and Lay?
I guess the real question is... Does the layout of the BA sheet change with any type of Market? Stocks, base ball, races etc, etc?

would just like to ask, do the trigger points i.e. MACD 30/70 levels need to be included in the control panel or will this go directly into the coding?

That's your choice. How do you plan to calculate the BA commands?

these will be variables I play around with when testing, I would simply like them to be in the most efficient place possible and to know where they are to experiment with.

All the variables you plan to play with should be available to the User. Than means they must be inputted into a sheet.

I have changed the WIP Workbook to be more User Friendly. However I think this workbook will wind up being a Developers reference and the actual Helper App Sheets will be copied from this one. I had to put a lot in it that the User won't need, but the developer does need to know. It is also more code friendly this way.

I have to sleep now. CU L8R.

[offthelip]

Cperry: MACD does not have significant levels at 30 and 70, it is RSI that normally has trigger levels at 30 and 70. MACD is an indicator derived from price which retains a value proportional to price. This means that MACD values can be anything, they are not bounded between 0 and 100 which RSI is. Thus the only realistic trigger for MACD are the crossover at zero. I agree that the RSI trigger levels of 30 and 70 should be tunable from the custom settings sheet

[SamT]

If RSI 30, 50, and 70 are tunable, then rename them to their mathematical formula nomens.
RSI_30 = <> 30 is illogical

RSI_Bottom_Trigger_Level, makes sense to a code. Don't Know if it makes sense to the User and the Developer, but some Name will make sense to everybody. That is the Name to use.

You should be able to write out all formulas in VBA-English patois using only words and Variable names. No numbers at all.

Example ( an imaginary formula for this example)

'Inputs: RSI_series; RSI_Bottom_Trigger_Level; OTL_Time_Correction_Factor'
Output: RSI_Bottom_Trigger
'Note: May use last value of RSI_Series. TBD

Dim RSI_Above_Bottom_Trigger as Boolean

RSI_Value = Average(RSI_series * OTL_Time_Correction_Factor; RSI_Bottom_Trigger_Level

RSI_Bottom_Trigger = RSI_Value < RSI_Bottom_Trigger_Level And RSI_Bottom_Trigger_Level = True
RSI_Above_Bottom_Trigger_Level = False