How can we create custom watch list of stock? We will know in this video that how much did the stock open at, what was the day's high and day's low, what was the previous closing of the stock, what is the current price of the stock. Along with this, we will get here the figures of how much has changed in the stock today, both amount wise and percentage wise. After this, how much volume is going on in the stock, what is the market cap of the stock, after this we get the 52nd high and high of the stock. Take 52 weeks, after this we get the PE ratio of the stock . Here we get the percentage wise performance of the stock within 5 days and 30 days . After this, at the end, we get the chart of the stock with these three values. We can change, here we want data of 10 days, so we change it here, similarly, here we want data of 60 days, so we change this also, similarly this is the chart for example. We need a chart of 100 days. As soon as we change it here to 100, our chart gets updated, then we can change these three values as per our requirement, so this is the overview of this sheet, we only have to update the name, data. It will come to us through automatic update. Let's talk about how we will create it. First of all here we write the name in the heading. We put the name of any stock here. Here I am putting IRFC. After this we will come here. Here we need at what price the stock opened, the high and low of the stock, now here we will type a formula equal to finance bracket, we will select the stock name, put comma inside the quotation, we will write price , open the bracket, close it and press enter. We will come here, here we will apply the formula equal [music] inside the quotation we will write low, close the bracket and press enter, then we have these three values, at what price did the stock open, the day high and day low of the stock. It has come here, after this here we write Previous Close and here LTP means Last Trading Price, we can also call it Current Price, then for Previous Close we will apply the formula Equal Price and it will come out to us like this. We will apply the formula equal to find out the current price of the stock, we will find out how much has changed in the stock, how much the stock has gone up or down in a day, we will find it in both ways, amount wise and percentage wise, here we write. Change and here we write change percentage. Here we will apply the formula equal. We will select and put comma. Inside the quotation we will write change. Close the bracket and press enter. Similarly, we will come here. Here we will apply the formula equal google3. Close the quotation. And we will press enter, this change has come to us amount wise and this has come percentage wise, after this here we give the heading Volume 52 Week High 52 Week Low Market Cap and P Now we will come here, here we will apply the formula Equal Equal Google2 will close the quotation and press enter. We will come here. We will apply the formula inside equal. We will write lo 52. We will close the quotation and press enter. Similarly, we will come here. Similarly, we will find the market cap and P ratio of the stock here. We will apply the formula equal market cap, close the quotation and press enter. Similarly, we will find out the P ratio of the same stock. We will apply the formula equal g finance bracket. We will select the stock name, put a comma, inside the quotation we will write PE, close the quotation and enter. Now here we apply formatting. We will press Control A twice. Now clicking here we will center it and select it middle align. After this we will come above the market capital. Now here we will see in this We are not able to understand anything, we want the value to be shown in a simple way, if it is shown in crores or lex then we will come to the market cap, now here we will click on the format, we will get the number last option. Custom Number Format We will click on it, here we will type hash comma double hash zero, put comma three times, give space once and inside the quotation, we will write CR, we will apply it, the value will start showing within our crores, after this We will come here, here our P ratio is being shown to us here. But we want our ratio to be rounded off plus a sign of multiplication behind it. For this we will click on the format number. We will click on the custom number format. Here we will type hash comma and double hash 0. We will give space and write We will select the numbers like this: Volume 52 Week High and 52 Week Low. We will apply number format on these also, after this we will select the column containing Volume, we will click here twice. After this, now our remaining change and change percentage are their formatting. We will do something like this, if our stock goes up, then here the value should be shown inside the plus and also the value should be highlighted inside our green color. Similarly, if the stock goes down, then the value should be shown inside our negative and red color. If there is a highlight inside , then for this we will first select the value to be changed, after this we will click on Format Number Custom Number Format. Here we will type plus is comma double hash zero dot double. This code is done. We will apply semicalculus for the plus value. Type We will do minus hash comma double hash zero dot double, this is our code, we will apply semicolon again for negative value, we will type dash, if our stock does not move anywhere then we should not see any change here. Now here we will see color inside the value. For this to also show, we will come to the beginning, put the bracket, type square bracket, type color 50, close the bracket, after this, come in front of the minus, square bracket, here we will type red bracket, close it, we will select it and we will copy it here . We will apply it from this, similarly we will select the percentage value Format Number Custom Number Format We will paste the code that we had copied here, here we will put the percentage sign after zero, here also we will use the percentage. We will put the sign and apply it, then this change is being shown to us in this manner, so we will come inside the formula bar, we will divide it by 100. Now it is being shown to us in this manner, so now we will calculate the percentage. How much has our stock percentage wise changed within the last 5 days and within 30 days? Here we give the heading 5 day pert and 30 days. Now we apply the formula here. If we understand it a little carefully, then here we will give the formula . Let's put equal comma -5 -5 Why we have done this, we need the data of last 5 days, that's why we have made it -5 here, we will press enter, now here we will see this date is coming, whatever price of 5 days ago. That price must have been there 5 days ago, it has come here, now we will bring this price here, before bringing this price here, we do not need this and this, so first we will remove these and then our price. We will bring what is left here and separate it from here, here we apply the formula, equal index bracket, we will select it, type comma to comma to bracket, close it and press enter. Now here we will see the price coming here. That price is being shown to us here, now we will connect these two functions, so how will we connect these two functions, we will come here, from here we will copy this function, we will start selecting it from finance and Till the end, we will select it, we will copy it, we will come here, inside this formula, we will delete the range that we had selected, now we will paste the formula that we had copied here, we will press enter. If we do this then the price which was coming here is the same price which is coming here, now we do not need it, so we delete it, we move this formula from here to here, now here we will see. So here we were calculating the percentage, so we will change this price inside the percentage, before this we will come here, we delete it and here now we just write five, inside this formula we will come back here. We have done -5, we will select it and we will delete it, now here we will type minus and we will select this cell, once we will press f4 and we will freeze it, we will come to the beginning of the formula equal. After we calculate the current price We will select, put the sign of divide, we will come to the end of the formula, here we will type -1, we will press enter, then this is our formula which will show us the change percentage within the last five days, here we will come to the heading, we will click. Above the format, we will type number and custom number format, here we will type hash space, inside the quotation, we will write day space percentage, we will apply it, like this we will come here, here we will type 30, here we will click and click here. While doing this, we will copy its formatting and by clicking on it, we will apply the formatting, then here we will see that the formula which we had applied here, we did not need to apply it here, the same formatting which was there will come here. Like this, we will copy this formula and paste it here, now here we will see that we are getting an error in the result, we will edit a little bit inside the formula, we will come to the starting point inside the formula, now here. The reference is g2, inside the previous formula, here we had given the reference of current price. Here also by removing this g2, we will give the reference of current price here, now we will come here, we will remove it from here and here we will select Now we will make the stock name last, we will come here, we will delete it from here and here we will give reference of this cell, we will freeze it by pressing F4, we will press enter, which is our 30 days return, that is here. It has arrived , now once we will click here, we will click on the paint format, now we will select these two cells, which is our return, percentage wise will start showing to us, now here we will insert the chart of the stock, example. For now we want to insert a chart of 100 days. Inside the heading we type 100. We will select it. We will click on the format. We will click on the number and custom number format. Here we will type hash space inside the quotation. We will write day space chart, we will apply it, so now what will happen here, we just have to change the days, our chart will automatically change according to the days, so we insert the chart, we will come here, here we will apply the formula equal. Fines bracket, we will select the stock name, put a comma, inside the quotation we will write price, put a comma, work day, open the bracket, type today bracket, open bracket, close, put a comma, now here we want to insert a chart of 100 days, so here we will type - 100 will close the bracket, will put a comma, will type today's bracket, open the bracket, close, here what we told in the formula, we can get the price of this stock, whatever is the price of the stock from the last 100 working days till today, we can get it here. When we press enter, whatever will be the price of last 100 working days, it has come out here. After this, now we do not need these dates here, so we will remove them from here, we will come inside the formula of equal. After this, we will type index bracket, go to the last part of the formula, put two commas, type two, press enter, our date will be removed from here, only our price will remain here, now we will convert this price inside the chart, inside the formula. Now after equal, we will type spark line bracket, we will come to the last part of the formula, close the bracket and press enter, now we move this chart from here to here, now once we come inside the formula, here What we have typed - 10000, so from here we will remove 100 and here we will give reference of this cell, then we will select it and we will press f4 key, we will press enter, then this is our chart, we need 100 days. Here this is a chart of 100 days, if we make it 200 then it will be updated, similarly if we make it 250 then our chart will be updated according to these days, after this now our chart looks attractive so inside it we Let's add color. It will come inside the formula. In the last part of the formula, between these two brackets, we will type comma. Inside the bracket quotation, we will type chart type. We will put comma inside the quotation. We will write column. We will put semicolon inside the quotation. We will put color comma, now in the last we will type inside the quotation, color 50, close the bracket, we will press enter, then this chart of ours has been updated here, if here But your chart is not updated in green color, then remove the color 50 that we have typed here and type green here and press enter, then our chart will update in green color, then this Our formulas are complete inside the sheet, now we just have to do its formatting here. Before formatting inside the sheet, let us know how we will enter the name of the stock here. Now like our second stock is Bajaj Fun if here. If we enter direct budget fines then our values will not be updated. We select all these and drag them down. Here we can see that our values are not updated then how will we enter the name of the stock here? But we will come to put it, all the details of the stock of Bajaj Fen are being shown here , the name of the stock is written here, just above it, we will see, here it is written NSC and just next to it, B AJ Finance is written. So whatever is written here, we will simply see it from here and type it here. So here we type whatever is our data, it has been updated, we can call it stock ticker, we can call it symbol, like this, third. Here we update the stock, Ta Motors is the same symbol of Ta Motors, so we update it here, whatever our stock will be, we will update it like this, lastly here we apply formatting on it . We will select the complete data and click on the format, here there is an option of alternating color, above this we will click on whatever color style you like here, you can select it as per your choice, here I am selecting it. You can also customize it from here. After selecting the color style, we will click on Done, then this sheet is now completely ready. You can download this sheet. You will get the link to download it in the description below,