Printable View
Apologies, my WiFi has been down for over 36 hours now and I've run out of 4G on my phone until Friday so just had to drive to my family's house to get online and read your comments, I will reply to the above messages shortly now I'm at a working desk. (This reoccurring problem over the past 2 months or so is edging me even closer to purchasing a VPN soon to ensure it doesn't affect my trading).
Right, I'm going to shut up, read, think and compose my replies...
Did you mean to attach this or is it just a minor tweak for yourself? (Also, I'm using this as an excuse to try out the quote function as I'm aware I haven't done so yet :P).Quote:
I have changed the WIP Workbook to be more User Friendly.
Yes, at all times BA will use ‘Back and Lay’ instead of ‘Buy and Sell’ just because that’s the terminology of betting which this form of trading derived from. The layout will always be the same on the BA sheet which is perfect for us because the code can always follow the same template, all I will be doing is trading the difference in opinion of price of… whatever. Like I said previously the first line could be the score line 0-0, a horse name or anything really (as shown in my previous attachments).
I’d like the trigger points to go in the coding directly please as I imagine this will be more efficient. I imagine the BA commands to be triggered like so…. (Keeping it in the form as it falls out of my head before making a hash of communicating this idea in 'developer speak' just so you two can make sure my logic is...logical)...
If MACD, RSI & WOM have all triggered ‘1’ at least once within the previous (look back) period, then “BACK”, if not ‘0’ then do nothing. If all have triggered ‘2’ then "LAY”.
MACD –
IF Signal line (9EMA) was less than MACD line (12EMA-26EMA) but is now greater then “BACK”.
IF MACD line (12EMA-26EMA) was greater than Signal line (9EMA) but is now less then “LAY”.
RSI –
IF RSI was less than 30 and now greater then “1”.
IF RSI was greater than 70 and now less then “2”.
(We don’t need an upper and lower trigger limit. RSI will return a number 0-100. The trigger will be if the RSI reading is greater than 70 or lower than 30. If this can’t be detected in a function kind of thing then we could just have a constant i.e. RSI_Bottom_Trigger and in the control panel just type in the number ‘30’, keeps it nice at simple).
WOM – (Anything less than 0 then “1”. Anything equal to or greater than 0 then “2”)
So if we have the MACD line, smoothing factor, RSI etc on the sheet (as we already have) to be customisable and then within the code it is constantly checking these figures (in the given look back period) for these changes and when it notices all 3 changes within this period then BACK/LAY in cell L10 (BAReport). Intertwined with this, is the staking formula that will be incorporated later.
Cells B2:H33 on the control panel sheet? I assume there will not be need for 3x all of these settings as all brands will read from this same part of the spreadsheet i.e. all brands will have a smoothing factor of 0.9. If I were to change this single cell to 0.8 it would affect all the brands not just horse 1, am I correct?
Ok, I'm going to head back home now. Will commute to my family's place again tomorrow if the WiFi is still down so I don't miss out on, what has become, this big fun project! Feel free to leave me tons of messages and/or jobs in the meantime...
Cheers lads,
CPerry.
CPerry, I need 4 class modules. All they will be are repositories. They will only contain a list of Private Property Variables, and, the Public Get and Let Property of each variable.
The Variables will be named after the Developer Nomen in the tables on sheets "BA Descriptor Tables" and "Formula Parameters," in the attached. Be sure to prefix the Var names with a "p".
Add the Private Property Variable, "pName," and it's Properties to each Class.
Name the Class Modules, "clsInfoFields," "clsDataFields, "clsInstructionFields," and "clsFormulaParameters."
I have created 4 class modules and named them as you requested and simply copied the variables as they are in the tables on the sheet. I’ve made a couple of comments on the ‘Control Panel’ tab on the document that I am attaching to this message regarding the information that should be shown in this section. A lot of the addresses on the Descriptor Tables tab were wrong (unless I am mistaken on how you had labelled them in which case just use the cells you typed in version 1 – the cells in red text though were completely missing either way so I have added these in too. I have made several tweaks around the entire document, nothing drastic.
Now am I correct in assuming the following cells on the ‘Control Panel’ tab will display the following?:
MACD_A: 12 EMA
MACD_B: 26 EMA
Signal: 9 EMA
EMA: Obviously the EMA
RSI Signal: Number
RSI 30: True/False if the RSI line crosses the 30 mark (the direction of the cross matters)
RSI 45: “”
RSI 55: “”
RSI 70: “”
WOM: Number (between -50 and 50)
If so, and the code is checking what the previous command given was (as I briefly discuss on that attached document) then I just need each of these indicators to show either BACK/LAY/CLOSE TRADE or nothing. If all three triggers display the same thing as each other (within the given look back period) then a BACK/LAY/CLOSE TRADE order will get placed in cell L9 on the Bet Angel tab.
If you look at the Control Panel tab I have presented this with an example in ‘Brand 2’. As the numbers tick in cells C15:L15, the rows underneath are checking to see if there are any crossovers i.e. RSI Signal WAS above 70 but is now below so “BACK” (this stays on screen for a count of whatever the look back period is set as). If cells D17, H17 and L17 all show “BACK” at any point simultaneously then M17 (signal) will show back or the command can go immediately to the instruction fields on the Bet Angel tab (Cell L11 for Brand 2) and voila the system has gone from start to finish and an order has been placed!! Getting close now!
For now, would it be best just to use Boolean outputs to keep it from getting messy? Any upward signal crosses returns a “0”, any downward signal crosses returns a “1”.
Cheers gents,
CPerry.
Attachment 20911
1 and 0 might be Booleans, or they might not be, It depends on outside variables. True and False are always Booleans, Period.
Now, I'm off to see your attachment.
Back from the attachment.
in re the Class modules:
Where are the Property subs associated with the variables?
Where are the pName variables and Properties?
I see you changed all the Row/Column references in these Tables
Data Fields for All Brands _______ Instruction Fields for All Brands Developer Nomen Column/Row Developer Nomen Column/Row BAInputTable_FirstCell B1 BAReport L9 BAInputTable_LastColumn 10 BAOdds M9 BABrandName B9 BAStake N9 BABrandPnL C9 BAStatus O9 BABrandClosePnL D9 BAMatchedOdds P9 BABrandGreenUpPnL D10 BAAmountMatched Q9 BAThirdBack E9 BABetReference R9 BAThirdBackVolume E10 BATimeOfTrade S9 BASecondBack F9 BAUnmatchedAverageBack T9 BASecondBackVolume F10 BAUnmatchedBackstake U9 BAFirstBack G9 BAUnmatchedCount V9 BAFirstBackVolume G10 BAUnmatchedAverageLay W9 BAFirstLay H9 BAUnmatchedLaystake X9 BAFirstLayVolume H10 BAUnmatchedLayCount Y9 BAThirdLay J9 BAAverageBack Z9 BAThirdLayVolume J10 BATotalSelectionBack AA9 BASecondLay I9 BABackCount AB9 BASecondLayVolume I10 BAAverageLay AC9 BAPreviousPrice K9 BATotalSelectionLay AD9 BABrandVolume K10 BALayCount AE9 BAAWP AF9
I can live with 9 and 10 as row references, it's just a little bit more code. BrandRow1 and BrandRow2, was just easier to code for.
But, the BAInputTable_FirstCell is not Range("B1")
Also, the Instruction Fields table should have FirstCell and LastColumn variables.
You might as well change the LAst Column Reference to the actual Column Letter. It's too easy to make mistakes when counting columns to use the column number.
I would have liked to see a row inserted above the Data Tables on the BA sheet, But your addressing scheme means that everytime you change the layout of the BA Sheet, you would have to edit both the Descriptor tables Row numbers. If you don't like ColumnLetter& RelativeRowNumber, it is Code friendly to use:
ColumnLetter & ":" & "Upper|Lower"
For examples
BABrandClosePnL ------> D:Upper
BABrandGreenUpPnL ---> D:Lower
This schema provides for changing Layouts with minimal editing and minimal chance for editing errors. I think it also makes more sense to the BA Developer, (that be you,) who is the one who must set all these values in a code friendly style.
BTW, good job on adding those new Instruction fields. :beerchug:
How I did it
Data Fields for All Brands
Instruction Fields for All Brands Developer Nomen Column/Row Developer Nomen Column/Row BADataTable_FirstCell
B9 BAIntructionsTable_FirstCell L9 BADataTable_LastColumn K BAIntructionstTable_LastColumn AF BABrandName B:Upper BAReport L:Upper BABrandPnL C:Upper BAOdds M:Upper BABrandClosePnL D:Upper BAStake N:Upper BABrandGreenUpPnL D:Lower BAStatus O:Upper BAThirdBack E:Upper BAMatchedOdds P:Upper BAThirdBackVolume E:Lower BAAmountMatched Q:Upper BASecondBack F:Upper BABetReference R:Upper BASecondBackVolume F:Lower BATimeOfTrade S:Upper BAFirstBack G:Upper BAUnmatchedAverageBack T:Upper BAFirstBackVolume G:Lower BAUnmatchedBackstake U:Upper BAFirstLay H:Upper BAUnmatchedCount V:Upper BAFirstLayVolume H:Lower BAUnmatchedAverageLay W:Upper BASecondLay I:Upper BAUnmatchedLaystake X:Upper BASecondLayVolume I:Lower BAUnmatchedLayCount Y:Upper BAThirdLay J:Upper BAAverageBack Z:Upper BAThirdLayVolume J:Lower BATotalSelectionBack AA:Upper BAPreviousPrice K:Upper BABackCount AB:Upper BABrandVolume K:Lower BAAverageLay AC:Upper BATotalSelectionLay AD:Upper BALayCount AE:Upper BAAWP AF:Upper
Ahhhhhhh ok, if BrandRow 1 and BrandRow2 is easier then stick with that, was just a little unsure how everything was being read and wired up. If the previous was correct just stick with that. Also with regard to the schema, I am unfamiliar with “Upper/Lower”, keep it as is please; again I’m just getting to grips with how everything is wiring up. I had to get rid of that row above the Data Tables because the Instruction Fields were all one row off when linked through BA. If this row can be squeezed in anywhere else, feel free to re-add it. If it goes in the previous position, I’ll just have to move the Instruction Fields down a row is all.
Why thank you! Does all of that coloured area that I created make sense?
I don’t think I follow. What has got to go in the class modules exactly? Can you see the 4 I created with the list of Developer Nomens in each? I was meant to ask this in my previous message but what properties does each require? Is this where the individual indicators coding gets placed? I realise this stage is really starting to test my ability now so I apologise if I continue to make silly mistakes/comments. Please know I am trying my best to keep up, read, learn and grow every time I come back to these messages on a daily basis.
"Upper" stands for the upper data Row of this Brand. "Lower" stands for the lower data row of this brand. All Brands occupy 2 rows across the sheet. I think that "upper" and "Lower" are generic enough to be technically correct, where both "1 and 2" and "9 and 10" are too specific to fit all cases.Quote:
I am unfamiliar with “Upper/Lower”, keep it as is please; again I’m just getting to grips with how everything is wiring up
Sorry, I thought it was apparent that both Data and Instructions had to move in tandem.Quote:
I had to get rid of that row above the Data Tables because the Instruction Fields were all one row off when linked through BA. If this row can be squeezed in anywhere else, feel free to re-add it. If it goes in the previous position, I’ll just have to move the Instruction Fields down a row is all.
Each Variable, including the pName Variable, needs a Let Property sub and a Get Property SubQuote:
What has got to go in the class modules exactly? Can you see the 4 I created with the list of Developer Nomens in each? I was meant to ask this in my previous message but what properties does each require?
See this: http://www.cpearson.com/Excel/Classes.aspx
In this page, in the Class Basics section, he has three Private Property Variables and simple Get and Let Property Subs for each. That is all those four Classes need. It is also the absolute most basic coding of VBA Classes you will run into.
For more info in re Classes, DuckDuckGo "VBA Class Modules"
That's where the indicators get stored for use throughout the Helper App. I haven't decided yet the best way to put the values in them. I want to keep RunTime overhead low. I have already HardCoded an offspring of the three Descriptor Classes to use while testing the BA Output and Helper SheetWriter.Quote:
Is this where the individual indicators coding gets placed?
I don't know your finances, but I strongly recommend UltraEdit for any serious coder/Programmer. Somewhere I have, or used to have, a UE macro that turns a list of Variables into a full set of Get/Let Subs.
Evening SamT
So am I correct with what I'm doing with the following code, would you mind just proof reading this please?:
This is the clsDataFields class module. I start by identifying what I believe to be the variables all 'As String' in a list and then I've written the first bit of code for the first variable on the list (pBAInputTable_FirstCell). If this is correct I will repeat this kind of format underneath for the rest of the variables in this list as well as all of the other variables for the other 3 class modules. Is my example correct?
I've just has a little look at UltraEdit and for £100 if I can start making a bit of money this will be one of the first things I reinvest in, there is also a trial version which looks like good value.Code:Private pBAInputTable_FirstCell As String
Private pBAInputTable_LastColumn As String
Private pBABrandName As String
Private pBABrandPnL As String
Private pBABrandClosePnL As String
Private pBABrandGreenUpPnL As String
Private pBAThirdBack As String
Private pBAThirdBackVolume As String
Private pBASecondBack As String
Private pBASecondBackVolume As String
Private pBAFirstBack As String
Private pBAFirstBackVolume As String
Private pBAFirstLay As String
Private pBAFirstLayVolume As String
Private pBAThirdLay As String
Private pBAThirdLayVolume As String
Private pBASecondLay As String
Private pBASecondLayVolume As String
Private pBAPreviousPrice As String
Private pBABrandVolume As String
‘First Cell Property
Public Property Get FirstCell() As String
FirstCell = pBAInputTable_FirstCell
End Property
Public Property Let FirstCell(Value As String)
pBAInputTable_FirstCell = Value
End Property
Looking forward to your reply,
CPerry.
You've got the idea.
Now just Follow standard Naming Conventions. And declare all these as RangesLater we will need to add Cell Address Get Subs. These will not need their own Private Property ValueCode:Private pBAInputTable_FirstCell As Range
‘First Cell Property
Public Property Get BAInputTable_FirstCell() As Range
Set BAInputTable_FirstCell = pBAInputTable_FirstCell
End Property
Public Property Set BAInputTable_FirstCell(FirstCell_In_BA_InputTable As Range)
'When the Coder uses this property, the input Parameter Variable Name is visible to him/her, So I use it as a hint.
'Set Property Sub are used when the Property is an Object of some Sort
Set pBAInputTable_FirstCell = FirstCell_In_BA_InputTable
End Property
Note how all the names relating to this property are so similar to each other. Also note how each Name exactly describes what it's talking about, ie a Cell or a Cell Address. This is one way to make wrong code stand out to the eye. It also means that one only has to remember the Naming Convention to correctly use the property in any code.Code:Public Property Get BAInputTable_FirstCellAddress() As String
BAInputTable_FirstCellAddress = pBAInputTable_FirstCell.Address
End Property
I really like writing my code as if it was an instruction manual. It takes too long, otherwise, to figure out what I was thinking 6 months after I wrote it. The verbosity of the Source Code, (VBA) does not effect the size of the Compiled Code, (hidden in the xls(m) file,) and may actually improve it's efficiency.
Attachment 20925Attachment 20926
Please find attached two documents:
1) The most up to date WIP master file with all four class modules completed.
2) A word document of 'Get Subs' that I made separately as you said we didn't need them yet - when we do they can just be copy and pasted from this document with ease.
It took a long time to go through all of that code but I actually quite enjoyed it!
Cheers,
CPerry.
That's very good to know. It means you're worth teaching. And believe me, this is a crash course in advanced VBA and Excel Programming.Quote:
It took a long time to go through all of that code but I actually quite enjoyed it!
The Address Property Subs , all the property subs looked good. I Refactored the Data Fields Class, adding in the Address Subs.
Since you are now very familiar with simple Properties, I drastically changed the layout of the code in the module. You already understand how Properties work, as a coder, all you're interested in now is that they work. This style of layout is only applicable to this simple type of Data Storage Class.
The only thing left to add is the Terminate Event Handler, but that is nothing to a good search and replace editor like UE.
Making a Property like these into oneliners just means replacing all End of lines in a Sub with ColonSpace, (": "). I did it by hand, With C&P. Then I used Excel to sort them alphabetically, then Cut and pasted them into the order I wanted.
I also found one Set Property with the pVariable name. Well VBA found it when I Compiled the project. Lucky me it was in the Class I was working on.
Anyway, you can add the Address Property Subs. Change the layout if you want. Then these will be ready to use.
Note that using the layout in the One I did made the bad Name stand out, since it was the only one that started with "pBA..."
Hi,
I have been away for a week and so I haven't been following progress on the workbook. Mosty of it look Ok, however the Parameter Input section doesn't make sense:
According to the original definition on MACD, RSI and WOM, there is a direct relationship between the "series count" and " the "Smoothing" so you don't need both.Quote:
macd_A_SeriesCountmacd_A_Smoothing
macd_B_SeriesCount
macd_B_Smoothing
macd_C_SeriesCount
macd_C_Smoothing
rsi_SeriesCount
wom_SeriesCount
wom_Adjuster
wom_BACK_TriggerCount
wom_LAY_TriggerCount
ema_Multiplier
In the original definition of the calculation the assumption is that interval betwen updates is fixed, so the "smoothing" is calculated by the equation:
smoothing= 2/(seriescount +1)
Because we don't have a fixed update interval we need to modify this equation by our " time since last update modifier" which I assume will be specific for each Brand. This value comes from the Brandupdate with each new value, and so is not a parameter
So the only parameters we need for these calculations are :
macd_A_SeriesCount
macd_B_SeriesCount
macd_C_SeriesCount
rsi_SeriesCount
wom_SeriesCount
I am not sure what:
wom_BACK_TriggerCount
wom_LAY_TriggerCount
are supposed to represent so I can't comment, is this supposed to define the "lookback period" for all triggers??
What does appear to be missing from this section is the trigger points for each of these three indicators. I would expect to see:
RSI_Low_and_Rising_trigger_point
RSI_High_and_Falling_trigger_point
RSI_MID_and_Rising_trigger_point
RSI_MID_and_Falling_trigger_point
MACD_Signal_Line_Cross_Zero_And_Rising_Trigger
MACD_Signal_Line_Cross_Zero_And_Falling_Trigger
MACD_Histogram_Cross_Zero_And_Rising_Trigger
MACD_Histogram_Cross_Zero_And_Falling_Trigger
WOM_Low_and_Rising_trigger_point
WOM_High_and_Falling_trigger_point
WOM_MID_and_Rising_trigger_point
WOM_MID_and_Falling_trigger_point
I hope this helps.
I have another query about the input parameters: the first field is:
This doesn't make sense to me at all:Quote:
Number of Updates before triggering VBA 10 Update_Start_Trigger
To get the maximum throughput before you start losing updates you need to "Minimise that maximum calculation time" as I stated before.
Assume we get an update every 20ms, and that the update takes, 5ms to do the update. If you wait until every 10th update to do the calculation you will be "wasting" 15ms of possible calculation time every update. After 10 updates you then have only 15ms to do all the calculations otherwise you will miss the next update. The way to use the available time most efficiently is to "spread" the calculation out into 10 different calculation bundles:
For example:
1st iteration; you do the MACD 12ema calculation
2nd interation: MACD 26 ema calc
3rd MACD signal line calculation and trigger
4th MACD 9ema calcaultion and trigger
5th RSI Ups calculation
6th RSI Downs calculatiom
7th RSI calcual and trigger
8th WOM Up calcs
9th WOM Downs calcs
10th WOM calc and trigger
This is the standard way of maximising throughput in realtime systems. Commonly it is much more complicated than this where different parts of the calculation are at different rates varying from once every 0.5ms to once every 50ms
So my conclusin is we need to trigger the VBA every update and schedule the appropriate part of the calculation using a scheduler.
@Cperry:
One thing which is worth trying before you go live is to do some back testing of your "rules". To do this all you need is record the price and time data for at least one horse and then add the lindicator calculations to it afterwards and see what would have happened.
Can you modify Sam logger to record just the price, Money , and the update time for one horse during a race sometime. If you post the data I can add in the indicator values easily using my existing functions.
Perry wanted the additional MACD Smoothing.
You, and I, think there is enough time between update Sessions to perform all the calculations, so I don't think the Update_Start_Trigger will be used.
Each UDO has one Write Property that Triggers the UDO.
I did copy some of your posts to the Code Notes file I keep.
wom_BACK_Trigger, Count wom_LAY_TriggerCount, and similar, are used to keep track of the number of Backs or Lays in a row, since Perry wants to insure a trend before Instructing BA with a Back or Lay Command
Quote:
the assumption is that interval between updates is fixed,
Times in Seconds
Batch UpDate Duration Idle between Batch updates Duration + Idle (Batch) Averages --> 0.0395726139 0.0901257803 0.1296983942 STDEV --> 0.01113843 0.01966182 0.021211519 0.0263427219
0.066185092 <-- Mins
0.0629596455 0.148812422 <--Maxes
Statistically, are those time differences large enough to worry about, or can they be treated in calculations as fixed intervals?
Only the Info Data is Updated in a roughly singular fashion, the Data Update and the Instructions Update are pure Batch updates, ie the updates are for all Brands/Horses at once. The Update Times for all Brands are identical.
My thoughts on timing:
When $C$2:$C$6 changes, it triggers the BA Worksheet Module to load data into the BAOutput UDO.
The BAOutput UDO Pushes the Data Store to the HelperInput UDO.
Each subsequent UDO in the Helper App is triggered by certain input Properties being accessed by the HelperInput UDO. Basically, I am designing the UDOs to have a LastInput_Received Event
When the HelperSheetWriter UDO receives it's last input from the previous Helper UDOs, it batch Updates the entire Control Panel Sheet.
If the Helper App can do this in >=66ms, we're golden for calculating all BA Updates: And the Helper App and the Bet Angel App can live in one Excel Workbook. I am maximizing Helper App Throughput as best I know.