How to Build a Trading Simulator using Excel VBA

hello and welcome in this video I'm going to show you how to build a trading simulator from scratch using Excel VBA a training simulator allows users to view prices as if they are happening in real time and this allows them to practice trading having a simulator like this will allow you to practice this trading with any type of sample data that you have most of the code in this application will be written using class modules so if you want to see a real-world application built using class modules then look no further make sure that you download the source code for this video from the link in the description if you liked the video please click on the like button and get notified of any new videos that are released click on the subscribe button and the bell icon beside it so the first thing that we're going to do is have a look at the trading simulator that we're going to build so this is our trading simulator and what it essentially does is the chart moves through two prices that we have and it adds one new price each time so it looks like it's reading through the data sequentially which is actually what it is doing pretty much so if we look at the prices that we have on this worksheet we've just got example set of data and there's a thousand prices and you can see in the right-hand side diagram of how the prices go up and down so in our player we can set the data range to be whatever we want we could say at 5:01 we could say we want a thousand and one and we can set it to whatever range our data is at and we can set it to any worksheet it's very useful because often when we run that simulation we want to try it on different pieces of data now the player settings up here frame weight is basically how many points that can be shown at any time so the maximum number of points the move interval is how many new points we show we're running out of speed of normal so every second it will add one new point now from move interval was five it would add five new points so you can see our speed over here is normal and if we press play you'll see that we get a new point every second now as the points come true we can actually change the speed if we want to we could set the speed to be 4x and you can see now that the points are gone much faster now at any point in the proceedings we can just click pause and we can look at the data at this day and we can use the left and the right buttons here just to move one at a time now these move based on the move interval so if we set to move interval to be ten you can see they will jump back 10 so really very useful the way that we can configure how the chart works now one thing we can also do and let me just put this back to one one thing we can also do is we can use our slider so our slider allows us to move to any position and if we move it again you can see we're at eight hundred and two and we can move it right very close to the end here now we can actually move it to the end obviously but I'm moving too close to the end just to show that when we press play it will actually stop when it reaches the final value so once it reaches the final value we can't go right anymore but we can go a left if we want and we can go right and go back now if we click on the reset button it just resets the chart back to what it was so this is very useful for practicing trading in our data now what I'm going to show you today is how to build this training simulator but once we have the trading simulator in place we could add other very useful things to it so let's have a look at this one so we press play and you can see that it's plane is normal and if we click on this we can add trend lines now in this case I've added two moving averages of different lengths and we can just click to have them off again so this is very simple to do and we could do this we could have the configuration of the moving average lengths on our worksheet if we required now you can see down here we've got a little kind of trading interface now with this trading interface what we could do is we can see the latest prices are coming in so at any point we can click OK let's buy and now we're buying and you can see the latest it goes between red and green depending on whether it's currently at a profit or a loss and so if it goes on to a profit we could just say ok let's sell or maybe we sell because we want to get out so what's going down and we let's say we just say ok let's sell it now you can see it shows our buy or sell and our profit so also another possibility for something that you could do once you have the trading simulator so what I'm going to look at today is the basic simulator itself and once you have that has a set there's a lot that you can do so let's go ahead and start coding the simulator a quick pause to tell you about the excel vba handbook course are you struggling to build VBA applications do you find it difficult to get good information on how to create real-world VBA code is it a struggle every time you try to create a VBA application no matter how simple it is well the excel vba handbook course teaches how to build real-world Excel VBA applications from scratch unlike most courses you won't be overwhelmed with information and left to figure out how to put it all together instead you'll be taken step by step 210 Excel VBA applications where every concept explained once you start working to VBA applications you'll be amazed how quickly your VBA skills increase so check out the VBA handbook course at the excel vba handbook comm and the link can also be found in the description below the video so let's press alt F 11 to enter the visual basic editor and then once were there we're going to create a settings class so the settings class is simply going to hold variables and these variables are going to be used to store the different settings on our worksheet so for example we're going to have public and one have current position and that's going to be a long and we're going to add one variable for each of the different settings that is on the worksheet once we have all variables added we want to create our second class 10 and our second class the function of that is to read in these settings so we're going to call that class 2 settings reader class we're going to start off by creating a Const variable what a Const is is basically a variable that won't change or can change as our code runs and we use this to store the cells where our configuration data is so the advantage of this is that we're going to be storing them in one place and this variable cannot be changed by some other code so if in the future we decide we want to write a configuration setting somewhere else to a fire or something it's very easy to update it from being a Const in to reading it to a file the key thing to remember is that we just have the cell value defined in one place in our application so I've created a read from sheet table what we want to do is we want to have a private variable and the private variable will be for our worksheet so this is the worksheet which is going to be the player worksheet and then we're going to have a second private variable and this is going to be the class module that we created so by having a private as new we're creating what we say an instance of that object so basically means that we can use the variables that we've just created now we use our initialize class here so every time a new object so our class module is created whenever we use that what happens is the class initialize is kicked off so another program in damages we call this a constructor so when our class initialize happens here we're going to set M sheet settings to be the sheet player we do it like this because in the future we could change how we decide what she were reading from we're gonna call a week from sheet so as soon as we use new for this class it's going to automatically read the data from the sheet and it's going to store in the variables in our CLS settings object so this is how we're going to do it so I'm just starting with current position once we can see that one works ok it's very easy then to add the other ones so we're basically saying the current position variable in our settings object and we want that to equal to our sheet and Tudor range and of course the range is what we've defined in our constant above and we use value just to get back the value so the easiest way always to test out if our class is working is just to create a test and that's what we're going to do in the moment now what we want to have here is the properties and the properties allows us to give back two different values so the easiest way to create the project that property is by using insert add property and we're going to call this one current position so we click OK it creates a let and a get so this means how we assign the property and how we read the property back now if we get rid of the left it means that the property is read-only because we don't want we don't want current position to be set because we're reading current position from the worksheet so we're saying if someone looks for composition we basically read it out of the settings and we delete the left because we want it to be read-only we don't want someone to set the composition to the reader class from outside the class so this is one variable that we have its current position we're going to test this out by using a test module and we call this test module so it's very very easy to see that it's going to be used for testing only and and when we're finished we can delete it so we don't have to take it too serious and we don't come back later and think what what we're using it for so we declare our object reader as new and so what this will automatically do is kick off the read and then we'll just do a debug print and and we'll get back the current position from the property so we run the code to see what exactly happens and you can see that it wrote one back because our current position is one so that's the value in the cell so if we step through the code you can see when it creates the new setting the settings reader it does class initialize and that calls read from sheet and the read from sheet places the value from the worksheet in current position and then we go to the current position property and that will return the current position so very simple once we have this in place then all we've got to do is we've got to add it for all the other variables so the key thing always is just get it working for one once you're happy that it works for one it's very easy to add it to the other ones so let's add a second variable so that we can see that it's working correctly for the second variable and once the second variable is okay we'll just go ahead and add all the rest so again we take the number of prices we're going to create a property and that property is going to read from the number of prices in settings now we add number of prices as the property name added as the settings variable and then that's just going to be what we're going to get back from the property then of course we want to read the value so when we do a read from sheet we're going to read it from the position so we're just going to be reading to the variable number of prices from settings and we're going to be reading from the Const for the number of prices so doing our code this way it makes it very straightforward makes it very readable as well we can see exactly what's happening and we can spot hers so we want to test what we've just done so we're going to use that number of prices to make sure that this won't work correctly so we'll go ahead and run the code and you'll see that it brought back the number of prices has been 1,000 so now that we've got a pretty solid understanding that it's working let's go ahead and add all the other ones pretty quickly so just copy and paste the constants the properties and the reading of the settings because we've already seen twice how they work now we've got two different types of properties that we want to add here as well so one of them for a slider we have what's called a linked cell so we link our slider to the current position and that's how the slider works so we'll be looking at that a bit later but a current position is n 13 so what we're going to do is we're just going to pass it back to actual constant because the constant has the actual cell address so the constant has the cell address and so that's what we want so we're just going to basically used constant in this case and will be given that address to the slider we'll see this in a later section now the other one that's a little bit different is our playing setting what plane does is it allows us to know whether we're currently playing so we're currently running through the different prices or if it has stopped so if we click on stopped we want to change that status and that will basically determine if it's running currently or if it's playing currently so we have playing or we have stopped on the spreadsheet so we want to convert them into a true or false essentially um how we do it is really quite easy so we simply check if the value in the position if it equals to plane so we say whatever is at the range and we use the cell and that is the status and what we say is if what's at that equals playing then what we want to do is return true so if it equals playing it will return true if it doesn't equal thing it will return false now we do the same thing when we're setting if we want to set clean to false we basically pass it the status and then we just assign the status to equal to if it's true we set it to playing and if it's false we want to set it to stop so we use a an i if statement here so this is like an if statement in excel the first part is a condition the second part is if it's true and the third parameter is if it's false so we'll use this later say when we're playing our prices and running children or when we're trying to stop it so the next thing we're going to do is create our CLS chart now the first thing our chart is going to have is a chart variable and this is going to be obviously of type chart so this allows us to reference the chart the next thing we want to do is add an initialized sub so we select class from the Left menu and this automatically creates the initialized so now the initialized table automatically runs when we create a new instance of a class so what we wanted to do is as soon as we create a new instance of this class we want to set it to the on our player worksheet and this is how we do it we use chart objects and the name of the chart so now we want to add the property for setting the data so this is something we're going to be doing later but we'll just add the property now so at any point when we're using our simulator we can change that so what the source data is and this property allows us to do it so it's very simple we're just taking the range as a parameter and we basically just passing that to the set source data so now the next one that we're going to look at is the most important one and it's kind of the core part of this simulator and that is the sub from moving the chart so this is going to take a frame as a parameter so the frame is a very simple class and we'll be creating that in a moment and then once we have the frame which is basically our start and in position we'll be setting the chart position what does setting the chart position mean so let's have a look at that now so before we write the code to move the chart let's look at the working version of the simulator to understand what it's doing so we've got a thousand points loaded into our charter what this means is that when we're going through the chart all were ever doing is looking at a certain frame of data so in this case we're looking at 200 points so we're looking at 182 up to 381 now if we move the chart to the right so we click on write or actually doing is just moving our frame so now we're starting at the point 180 tree and we're moving to the point 382 so as we keep moving to the right it looks like we're getting new points in even though the points exist in the chart but the simulator makes it look like we're getting new points in and so it simulates like it's doing live trading so what we what we want to do to achieve this is we want to format our access so we right-click on this axis and we go to format access this one here which is the minimum and the maximum so you can see it says minimum want it to maximum so what we do now let me just move the chart over is we just change these each time so if we move one to the right will change our minimum to be one a tree and see your chart moves slightly and then we'll change this one to be 408 and you can see that the chart moved on and that's essentially what we're doing so that's what we're going to be doing setting the minimum and maximum access now 100 thing that we want to do is if we move on the data you can see that the range of data changes and if we don't set the y axis correctly the data is going to be off so this is our y axis we're automatically setting it but just imagine that we didn't set it and let's let's just put it up by 20 and let's put the maximum at 22 so now you can see we've lost all the points on us now it might not always be that bad we could have something like this where we we see a certain amount of the points but the other points are missing so we want to set our y axis so that it always contains all the data and it kind of centers it on our chart so what we do basically is we get an array of all the points in that that are going to be shown on the chart and then we get the main a maximum value and we set the range to be one above the max and one below the min so if we just play as normal you can see that our points are always nicely positioned on our chart and you can see as they go up and get higher that the chart adjusts accordingly so let's go and write the code to do this now so remove chart soap takes a frame as a parameter so let's create that frame module now the frame module is simply a class module that has two variables and the reason that we have it as a class module is that it makes it much easier to pass it around so in the future if our frame had four variables then it would be a big change in our code to go around everywhere and change for the four variables but when we have it in one single object we've only got to make the change in one place so now that we have the frame set what we want to do is we want to set our axis and we'll start by setting the x-axis so Excel category is what we do to select the x-axis and XL value is for the y-axis that we'll see in a minute and then we use many scale and we used the start position so our first point will be at minimum scale and our second point will be at maximum scale which is our end position so once we have this done we want to go on and do our y-axis now the problem with the y-axis is that we want to get all the values between the start line position and we want to get the max and minimum of these values there's no real simple way that we can get the values so we've got to write our own function and what this function will do is give us back all the values between the first and last positions so we use full series collection that's all the data on the chart we use the start position and the end position and then this function will give us back an array of the values and then we'll set them using Excel value so what we use in this case is the worksheet function minimum and we pass it the array and this will give us back the minimum value from the array and we'll set our y-axis to be this minus one and we'll do something similar with the maximum except we'll set it to be plus one so let's go ahead and write this get current values soap or describe current values function so just in case you don't know the difference between a function and a soap in VBA is that a function returns a value and a soap doesn't so the first thing we want to do is check that the first isn't before one because sometimes the first that we have position on the chart may not have any values so if it's less than one we want to set it to 1 and if it is greater than the number of values we want to set it to be the last one so again this is because in some cases we can have points on the charts that are not actually values so once we do this we want to create our new array and our new array will be the size of the number of items at last - the first item and we want to add plus 1 so that gives us the total number of items so then what we do is we run through the array so we start the position first and what we'll do is we go to last now a position that we use here at the variable this is used as a position in our new array so we've created a far loop now for I equals PI equals first to last and then we set a rare new position which is position one at this point and this is going to equal the position in our current values variable so if the first position is 200 we're basically saying in the new array position 1 put put 200 the next time it'll be 200 and one will be put to position 2 and so on and then we finish our far loop and what we do then is we return the array using get current values assigned and so once we have it done then what we want to do is we want to test it out so to test we pull back to our test only module and we're going to create a simple soap so we declare our chart and this creates a new chart and then we could create a new frame object so we set the positions in the frame object on a set it from 100 to 300 and then once that's done we'll move the chart and we'll pass in the frame you can see how little code it actually takes to do this once we've written it as class modules so only a few lines of code that we need to actually move the chart so let's run this on our current chart and see what we get so we run the code and you can see that it starts at position 100 and that the last position on the chart is 300 so let's change the values and let's do it again just to show that it's working now this will form the basis of our simulator the way that we move the chart so we're starting at 450 and 750 so let's run our code again and see what happens you can see that it rankly it set our chart to 450 points up to 750 points so what we're going to do next is we're going to add the code that will move the chart right one when we click on right button so we start off by having our click events module so the click event modules hold all the different subs that run when we click on the different buttons on the worksheet so we put them all and click event sort of easy to manage and easy to organize and we always end our name with underscore click let's just put a message box in here just to ensure that it works right so it's going to be a very simple test so when we click on the right button we should see a message box so we go to the worksheet you right-click on the button and we assign macro this brings up all the public macros we just double click on move right so now when we click on move right you see that it brought up the move right message box so this button is now assigned to the move right underscore click soap so now we're inserting a class module and what this class module is going to be used for it's going to be used for moving the frames so this is going to be if we want to move a frame left we want to move a frame right or we want to move a frame to a certain position or to the start even so we'll start off by declaring the variables that we need so we have a frame as a private variable so we saw the frame already for the chart so this is what the chart takes and the frame basically just has an ending position and a start position and this is what we'll be giving to the chart so the frame weight is the weight of the frame so these are settings that we read from the sheet and we're going to be passing them into this class module so we want the frame with the number of prices and we wanted to move interval and we'll set these in our initial function so when we're finished with our frame when we move the frame what we do is we pass it back so we're going to set a property for the frame and this will essentially sent set back our send back the EM underscore frame so after we move we basically just get the frame and then we can use this frame to move the chart or to move a scrollbar or something like that now we have an innate function so in other languages we have a constructor so when we create a new instance of an object we can pass in parameters we can't in VBA so we have to create our own and it's up and what we're going to be doing is we're going to be passing in the different values so we're going to be passing in frame with number of prices and the move interval and then we're going to assign these to the private variables that we have so we're just splitting up this line just because I'm using a big text on my screen and this makes it just easier to read so we can split a line over multiple lines by using the underscore so now we take in all these values and we're just going to set them so m underscore frame our private member is gonna equal to the one we pass in so at the start when we create our new frame position object we're going to use in it we're going to set these values so then our frame position class has these two perform calculations with and what we do is we set an initial frame as well so we want to set the initial frame to whatever the current position is and we have a sub called update frame an update frame creates the new frame from the given position so if we set a new position is 20 it will create the frame for our position 20 so again we've one sub to do this because if in the future we wanted to change how we created our frames then we don't need change it in one place and that would be an update frame so the update frame is very simple it's just tune a calculation it sets the end position as the position and then just does a calculation for the start position and that calculation is - the frame width so very simple really so every time that we do some kind of movement we update the frame and as I said we update it at the start so that our frame has a position and we want to move the frame then we're going to move it from its initial position so here's our move right soap so when we move it right what we want to ensure is that we're not moving past the end of the values so we set the new position first and then we check that the new position is ok so the new position will equal the current position plus whatever our move interval is which is at the moment and now that we have the new position we want to check that the new position is valid we used a function check position is a tend to check if we've reached the last value so get rather than having assumed calculations and move right and in all the different ones where we're checking for the end we just have one check position at end soap so if in the future we decide Howard however I'm checking the position at the end is going to change we're just making that change in this function we don't have to make it anywhere else we don't have it in lots of places in our code so this it's very good to have a dependency on soaps like this so how we check it's at the end if position is greater than or equal to number of prices we don't want to move any further right so in other words if check position at the end so we give it a new position if this position is at the end then we won't do anything but if it's false then we're going to move it to the right so we basically update the frame as we saw already with the new position so in other words the new position equals the current position plus to move interval we check if it's valid if it's valid we update the frame if it's at the end we don't do anything as always when we create new objects is very good to test them under all and you can see the benefit of objects is that we can actually take these objects and test them in isolation from other objects and this makes the call very flexible indeed and it means we can do an awful lot of testing before we actually start using their class modules in our applications so we create a new frame position and here is the init and all we're going to do is pass it the values from reader so whatever the current position is that we read off the worksheet to frame with the number of prices and the mover interval and then we're going to move it to the right and we're going to check if this is valid so we can just use our chart that we created already and we just move the chart and you can see how easy this is we've basically used a few lines of code now that we have the modules declared we use frame position and we we just pass back the frame using the property of the frame position class module and we give that to our move chart this is one thing that we just have to do now we were just updating so this is something I'll be getting rid of Wimmer enough testing but we just have to update the current position after we remove the chart so we updated on the worksheet otherwise it won't keep moving right so let's go ahead and check it out so we set the value to 250 and let's run our code and see exactly what happens so we've just got to before we do that we've just got to do our test so we're going to just put our test reader in here and so when we click on the right button it's going to move us to the right bit using our test reader let's click on this button and you can see that it moved to 1 5 2 so we click it again you can see 1 5 3 1 5 4 up to 1 5 and so on and we keep clicking it right it looks like it's playing through all the different values so that's how easy it was to do or move right once we have our move chart created already so now we want to create the class and this class will take care of the movement so this class will create our frame position and it will call the move chart and do the various different things depending on if we're moving right moving left and so on so we call this class movement manager so we're going to have our variables in the class and variables we're going to have I'm going to be the frame position the one that we've just looked at so we're going to have that as one of the members of the class and we're also going to have to read so we can read the data and then we can move it based on proposition ring and based on what the user has clicked so we want to create a class initialize and again we always get that by just selecting class and Lola matically generate the initialize and initializes always kicked off when we create a new instance of this object so when we use CLS movement manager it will automatically create it will automatically run the class initialize and when we run that what we want to do is we want to set plane to false and the reason we do that is in more situations if it's playing and we want to do something else we set plane to false so playing is only true when it's actually playing you know circumstances it set defaults so we want to do something else we turn off the plane now we set the position so we have a position here and we're going to set the position to be the reader current position and we're just going to take our frame position that we were using in test we're just going to use DAF here so it's the same as we saw in test what we're actually doing here we're creating the frame position and we're passing it whatever the current position is on the channel so that's what it will do at the start so creates the frame position with the initial frame and then when we want to move it we can easily move it by selecting move left move right or whatever so this is our move right in the movement manager and we'll be calling this from our mod move so the mod move we've got we've created and this is where we're going to be creating the move manager from so our click events calls this one because we don't anything in our click event and then in our click event we create the movement manager and in the movement manager we say move right and so we'll just put it here in our click event called move right so if we click on move right now you can see that it works it actually moves right so we've got our code it all in place now let's put in a break point so we can actually see what's happening so we stop on move right we step into this one and this creates the movement object that we just created and we go into the movement object we set the sheet equal to the player sheet so we've created a new player and now we use the current position initialize the frame and then when we go to move right you can see we select move position to move right and this is what we saw already after moves right we have forgotten new frame and we give that frame to move chart and that moves it and then at the end we're setting the position back so we're setting the read or position so that it the position will be correct after we've moved so the code that we have here with the chart this can get a bit long-winded because we're also going to be updating our school and we could be updating a trade or other things on our worksheet and a problem is that for every move we do we're repeating this code and it gets a bit messy so what we're going to do instead is we're going to create a class and the class will take care of updating our worksheet we're going to call this UI manager so UI for user interface and after we do a movement this is going to take care of moving everything so moving the scroll bar moving the chart and if we had trades in place it would take care of that as well so it's very very simple what we're going to do we're just going to have an update position and this is going to take the frame so this would be the frame that's just moved and all we'll do is call the different subs that will update different things like the chart disorder scroller so we're updating the calling the update trade as well now this is just for an example I won't be implementing it in this video and it just goes to show that if with all these different things to implement how we would do it so we just simply pass the frame to each one of these and then it's very simple so our update chart the code for this is quite simple we just have a private member as the chart so the code is very similar to what we had in movement but the only difference is were only gonna need it in one place and we put it under update chart we simply call move chart and we pass it the frame as we've seen this done before but the difference as I said is that we're only gonna have to do this in one place in our code now we need to add an instance of this new class module to our movement class I'm a call a dua manager and would say as a new UI manager now then after we move right we just simply call the update so we update the position and we pass it the current frame that we've just updated so we've done a debug compile there was no errors so let's actually check if this code works with our new change so we click on the write button now you can see that we're getting new values as we click on the right button so what we're going to look at now is the scroll bar so the scroll bar is like the bar you see at the end of any video so if you look at any video website we basically can drag the video by using a scroll bar so we're going to use the same idea here and we're going to format to control the foot control is very simple it just has a minimum value and Max value whatever the current value is and it has the incremental change which means every time you move it how much it changes and the page change is when we just click on to the right of the icon and it moves on so in this case it's 10 now the cell link is very interesting this is the cell that has the position you can see a 1 has the position here and as we move the scroll bar you can see that that value Changez so this is very useful for us because it means if we change the scroll bar it'll automatically update the position and if we change the position it will automatically update the scroll bar so this is very useful indeed so let's add our scroll bar class and scroll bar class is going to take care of what we need to do to scroll bar and you'll be surprised actually how little code we really need for the scroll bar we really just need to set the initial settings and then we just need to set a position so can we have the class initialize so as soon as we create the scroll bar class we'll basically assign the scroll bar shape which is that the variable will assign this to the scroll bar on our worksheet so setting the scroll bar settings so this is the initial one that we'll do at the start so this will change when somebody sets a new set of prices as the source data we want to change what the main and the max is or if someone changes to move interval we want to update to move interval so we simply just take these values that are passed in and we update the scroll bar settings so these are the ones we looked at a few minutes ago on the scroll bar itself so we just set them into one the main actually should be set to min I'll just change that we set max to the max that we're getting in the move interval the small change is basically the move interval we'll change that to move interval the page change we don't have any configuration setting for that so we'll just leave it at 10 which is standard and then the link cell is going to be the link cell parameter that we've passed in so now we're going to set the scroll bar position and this one is even easier than the previous one we've just done we basically just take the position and we just set the value and we do this whenever the value has changed on the chart because we want to update the scroll bar so that their Darien sync basically so this is very simple to do we just use ctrl format and then we use the value when we set it to whatever the current position is so if we go back to our UI manager we're going to now add a scroll bar and our scroll bar so we haven't added a jet so let me just go up and qui here it I was jumping the gun there a bit so scrollbar has new CLS scroll down and so when we go down what we can do then is simply set the scrollbar position and we we basically use the frame but we're going to use the frame and position so let's try this out now we're gonna get an error because I have to actually update the frame so this should be this should be frame position and once we have the frame in position we run on the code so let's go back now that we have a correct and try it and you can see that the scrollbar is moving on as is the position so so let me move the scroll bar on it changes to position to 6x6 or three five seven or whatever but we don't have that change event in yet so it won't change the chart but you can see very little code really needed out the scrollbar and we were able to update it quite nicely so we're gonna add our school bar event now now this is a change event rather than a click event but it's much the same it just means this will fire this sub will run as soon as we change to scrollbar position and we're gonna call move scroll back which we haven't created yet so create another module and this module will have our scroll bar stuff and again this is just a very small module that just does the scroll bar and we'll use move scroll bar as our soap and so what this does is it creates a movement object because the scroll bar has moved we want to move to a position so if we move the scroll bar we want to move to whatever position to scroll bar and move to so we're going to use movement move to current so whatever the current position is so we have our move to current now and this one is just going to be moved to a position so move to whatever the position and we supply the position in this case so when we move right it just moves right by the interval but when we want to move to a position we give it the position that we want to move to so it's going to move to whatever the current is then we're going to call read from sheet and that will give us the current position just want to make sure that what the current position is up-to-date now we're gonna bring our frame position and this time we're going to move to a given position so this is our move to position and we take a position so the new position is coming in and in this case we obviously don't assign the position but we want to check that the new position is valid so interior the new position should always be valid because we're only the the range is set within the range of the scroll bar but sometimes it can be wrong so it's always good just to check for sure so just saying that the new position is greater than equal to one and the new position is less than the number of values so if that happens move it to the position if it doesn't happen we don't move it so generally speaking any time we move to position it should probably just move anyway it's just in certain cases there may be an issue so just have to change the name of this one moved the current we just have to rename it so let's go back to frame position and then we go to click events and let's move the scroller to see exactly what happens so it's not doing anything that's because we have to assign this to the change event so we do a sign macro and our scroll bar change event now whom we move it you'll see that I will stop in the code so we called the move scrollbar and it moves to current and then it does the normal thing does that last initialize sets are shade and so on so in a movement manager it's going to move to current it's going to read from the sheet again so we get the current position and then we're going to move to that position so in the position just checks that it's valid and then if it's valid updates the frame with the new position so let's run it now without any breakpoints we move it to position one and you can see that it's now at position one so now we can take it and we can drag it let's put it 402 and you can see that it moves to the position actually 4:35 so you can see that it moves to the position where 4:35 is the endpoint at 336 is the star point so we can move it all the way on 829 you can see tentacles from 7:30 to 8:00 29 and we can move it to the very last 1000 and it moves so you can see it's quite easy to add the scroll bar in terms that it doesn't really require a lot of cold but the result of using it is very very effective now that we've looked at the scroll bar we've got our infrastructure in place let's start adding all the other items that we want to do so we want to add left we want to add pause we want to add stop and we want to add play so these should be very easy to add no because as I said we've got most of the structure in place already so let's go ahead and create our click events for each of the different buttons on our worksheet so for example we're going to have left we're gonna have pause we're gonna have stop or reset and we're going to have play now once we've added these click events what we want to do is to assign the macros to so assign these subs from each of the buttons you so now let's go to Eric mod move module and in the mod move module we're gonna call the movement class in most cases but in one case you'll see that we'll be calling to reader class so move left is going to be the same as move right and we're just going to call a move left movement now we set is going to be moved to the start like we're resetting our values play is going to have a place open that's going to be different than how we're doing other things we'll see that just a little bit later and then finally pause suppose a bit different when we pause all were actually doing is setting playing to stopped and our play loop that we write later basically reads this each time it goes to the loop and if it's set to stopped it will stop playing so very simple so that's all that poses actually them just setting the value to stop time we do that by setting playing to equal to false so in our movement manager we're going to create each of the subs that we need we don't need one for pause so we've kind of dropped one move left is pretty much the same as move right and reset that we using is going to be moved to the start so play we're not going to do to play one yet and we're going to do move start and move left at the moment we'll get to play in a moment so play is the most complicated one but still is not too complicated we'll have a look at that in a moment so let's update the frame position for all our frames so move left very similar to move right the only difference is that we're subtracting the move interval rather than adding the move interval and of course we have to do a check and our check is if it's at the start rather than at the end and we check the position against our starting position so if new position is greater than equal to start position and the first position as I said before the first position is one but if you wanted to change that in the future it's good to be using get first position because we only need to change in one position now move to start very simple we're just going to be setting this to get first position you can see once we have the code in place and our structure in place that a lot of this is very simple so what we're doing would call up their friend we're calling two subs you've already created get first position and then update the frame so let's do a debug compile to check for errors and you can see that we have a move start now that should be reset so let's just change this and now that we've changed this let's go to the chart and test out what we've done so if we click right you can see that it moves right so this is the one we had already so let's go back and go left you can see that we're moving left and when it reaches the first item it will stop so it doesn't keep going which could cause us errors so right again and this time we're gonna reset click reset and you can see that it brought us back to position 1 so it did move to the start so now I'm gonna write playing here and then when I click on pause you'll see that it sets playing to stopped so when we do the player loop this will in fact be enough to stop the play loop running so the play loop is the most interesting one so let's have a look at how we do that so it's a little bit trickier than the other ones because we're playing it in real time rather than just moving one at a time so the first thing we do is set playing to true and then we do our do loop so we'll enter the loop and we'll loop until the frame position is at the end now we don't have in is at the end yet so let's write that sub and the second thing we check is playing his plane has plane bits set to stopped so if we've clicked paused and b2 somewhere in that loop every time it reaches the end of the loop it will check and it will stop so we wanna write is at the end now we've already got check position at the end we give it a position and say is this at the end I'm gonna want to run the same thing again we're gonna check position at the end we're gonna use our current position we're just saying are we at the end now so we say is at the end equals check position at the end and we give it our current position which is framed our end position so now that we have that what we want to do first of all is we want to move right so when we're playing will be we're basically just moving right and every time we move right we have some kind of pause interval and then we keep moving right and we'll keep doing that until the loop conditions fail so it's no longer playing or we've reached the end so we move right and just like when we move right normally we want to update our position so we update our position with the frame and now at this point we want to sleep or we want to pause so we pause for a second or something and disc and then we'll go again to the loop and go against your loop and this gives the idea that we're actually playing through the data then we read from the sheet so we read delayed us from the sheet so this is to make sure if stopped if it has been changed to stop that we're actually getting the latest value and we want to have a sleep loop now I'm giving the sleep loop a thousand milliseconds so let's go and see what our sleep loop is so we insert this module and I'm gonna copy and paste the code here so this is Windows API code how we use the API is like this so written a sub based on this we just use the get tick count and we basically get it to take count and we run through a loop and when the loop ends when we get to the end it it exits so this gives us the illusion of pausing now we have an error here is our end and that's because I put it is at end as private so I'll just make that public and yet everything is okay so let's press play so we press play it's just gone on to one so I think the problem the problem here actually is that I gave it a thousand milliseconds and it's actually in seconds so it should be actually a second that I'm giving it so if we go back now and run it again it should 1 okay so you can see that it's running every second it's updating and having a new point so if we press pause you can see that it stopped now one thing we want to do is the speed you can see the speed here is 4x and we're not actually the speed at the moment so what we were going to do is we're going to add just a simple function and the function converts the speed like for example they will say 4x equals quarter of a second and it does this using a select statement so we get in two different texts for the speed and we just simply convert that into a number which is Creech's in seconds so now that we've got that done we can just put it into our sleep loop as a parameter so get the time meant from speed and the parameter we've that is the actual speed and we get that very easily from our reader you can see again the advantages of having a good structure in place we don't have to look around or try and read from the sheet again we've already got that in our reader so now let's play now we're playing at the speed of 4 X which is a quarter of a second per interval and if we change that to normal you will see that it goes or it's just gone every second so 2x is every half a second and just put it back to 4x which is every quarter of a second in nine time we can press pause and you can see that it stops it so that's how we do our play so we're playing to all the data and this is exactly how it works so now we're going to write the code to update the chart so in other words if the user makes changes to the chart sheet or to the chart range this code will update it when we click on the update chart button so we're going to call the initialize and we're going to have this in another sub and this so very much art so initialize chart so we'll be in the mod chart so let's create this new module and we call it as a set mod chart so the first thing we need is the reader because we want to read the data and the different configuration settings so we want to read these from the worksheet and then we're going to pass these to our uie manager now remember our UI manager its job is to take care of the Tings on our user interface so such as the scroll bar the chart and if we had trading it would do that as well now we're going to have a new sub there we're going to call it reset controls and we're going to write that in our UI manager class module so let's add it to the top of the module and the other ones you can see we have update position which was very very simple so every time we move the chart we call update position to update the different controls so we're passing in the settings reader so the first thing we want to do is we want to get the worksheet so the worksheet that is specified on our worksheets or specified in cell b7 and we get this back from the reader the chart worksheet so the chart worksheet we give this two worksheets and this will give us back at that worksheet so once we have this sheet what we want to do next is we want to move to the very start of our frame so we want to move to the very first position on the chart so we use frame position to give us a frame so that will create the initial frame now we're going to use weight here so that we don't have to keep repeating reader everywhere so we pass frame weight current position number of prices and move into votes we pass these to the frame position so we moved in the frame to the start so we set the school bar settings now the scroll bar settings these are the initial settings of the scroll bar it's not the position so we basically say the number of values which is number of prices to move interval and the link cell which is the cell with the current position and so we're going to update position which now will actually update the chart update school there and anything else that we need and we pass it to the frame see not a huge amount of code and that basically resets everything let's update the chart now first of all we've got to do an assignment so we're going to assign that to an it suit to the update chart click event which is down at the bottom so if we click on update chart it resets our chart but it doesn't make it kind noticeable difference because we've the same value same range etc so we set it to 501 so we've now 500 prices and if we click on update chart it's updated the chart and when we drag the scroll bar you'll see that we've only got up to 500 prices now if we go back to 4:00 at 9:00 and press play it's going to stop at 500 prices because that's all that we have so it's a very useful setting on our worksheet allows the user to select a range of prices and the worksheet that they want to use so this is the completed trading simulator you can see we can play data from the start we can do things like change our interval now we have to stop if we want to actually update the interval because we don't have that within the loop so you just press stop press play again you can see now that it's got a new interval so it can configure all of this playing very very easily we can pause it we can move left or right we can do so many things so this is very very useful if we want to test our trading so we want to take test prices and we want to practice trading with it I hope you enjoyed this video if you've got any questions comments or queries please add them to the comment section below and don't forget that you can download all the codes that are used today in the description below now if you liked the video please click on the like button and if you'd like to get notified my of comment videos then click on the subscribe button and the Bell icon beside it hope to see you in the next video

Channel: Excel Macro Mastery

Views: 40,864

Keywords: excel, microsoft excel (software), vba, excel vba, visual basic for app, trading simulator, vba class modules, vba application

Id: vUg-NH-Wqss

Length: 59min 48sec (3588 seconds)

Published: Thu Jul 16 2020