Can somebody help me in making this formula dynamic in nature.

[SONJOE JOSEPH]

Hi:

Find the attached.

Thanks

Dear Nebu,

I was just trying to give another statistical formula to find out the Daily Volatility for both Nifty as well as the banknifty. But using yr logic i was not able to do it successfully. I was getting plenty of Syntax errors. Normally i used to find out the DV using the formula: =sqrt(average(E1075:E1084)-average(D1075:D1084)^2)) where E is the "LN-SQ" and D is the "LN". How can we apply this formula using excel vba code.

Looking for a solution and my working file attached.

Regards,

Sonjoe Joseph.

 

[Nebu]

Hi:

I have constructed a formula in the colored cell, check and let me know whether the formula is giving you the correct answer.If the answer is correct I can include this in the code.

Note: It's an array formula and will have to execute using control+shift+enter key, normal enter will not work.

Thanks

 

[SONJOE JOSEPH]

Hi:

I have constructed a formula in the colored cell, check and let me know whether the formula is giving you the correct answer.If the answer is correct I can include this in the code.

Note: It's an array formula and will have to execute using control+shift+enter key, normal enter will not work.

Thanks

Dear Nebu,

Good Morning Nebu. How are you? Well for the 13th May, 2016 the DV is correct. But when i tried to enter 16th May the DV is just coming blank as soon as i enter the date and closing price but rest of calculations are working fine. The formula which u have given is so complicated which frankly speaking i didn't understand. The formula which i have given was =sqrt(average(E1075:E1084)-(AVERAGE(D1075:1084)^2)) which will be the DV for 13th May,16. For the 16th May the DV formulas will be: =sqrt(average(E1076:E1085)-(AVERAGE(D1076:1085)^2)). If you notice both the codes after on the E and D part there is a 10 points gap which will extend as the day progress. For 17th May,16 the formula will be =sqrt(average(E1077:E1086)-(AVERAGE(D1077:1086)^2))

Hope now u got the logic...any clarifications just send a message. I'm sending the screen shot and the working file.

Regards,

Sonjoe Joseph.

 

[Nebu]

Hi:

Find the attached.

Thanks

 

[SONJOE JOSEPH]

Hi:

Find the attached.

Thanks

Dear Nebu.

Thanks a lot for your great help....you are just awesome in VBA macros. I was just going through the code which you have written:-

.Range("f" & dr) = Sqr(WorksheetFunction.Average(.Range(.Cells(dr, 5), .Cells(dr - 9, 5))) - (WorksheetFunction.Average(.Range(.Cells(dr, 4), .Cells(dr - 9, 4)))) ^ 2

Nebu can you please explain it to me after the worksheetfunction.average just would like to know.

The code is working perfectly fine i have tested it .This is a bit difficult code for me to understand Hope you will explain it to me in a simple way.

Regards,

Sonjoe Joseph.

 

[Nebu]

Hi:

Google it you can get oodles of materials on web.

Thanks

 

[SONJOE JOSEPH]

Hi:

Google it you can get oodles of materials on web.

Thanks

Dear Nebu,

Good Morning. I have made some changes in the Stock Test01 file. In the column "S" you will find "CD" ie Current Date and "SD" ie Start Date. Using VBA code following things to be done which are listed below:-

1. In the "CD" i want to use the excel function =TODAY() as vba code.
2. In the "SD" i want the formula as =CD-365 as vba code.

While coding this should not be included in the sub dailyreturn(). Do create a new sub called mydate() so that i can add some more date calculations so that i can achieve the desired task.

Attaching my working file along with this message.

Thanking You,

Regards,

Sonjoe Joseph.

 

[Nebu]

Hi:

Why you want code for this, the function today() is dynamic , yo do not need a macro for this.

Thanks

 

[SONJOE JOSEPH]

Hi:

Why you want code for this, the function today() is dynamic , yo do not need a macro for this.

Thanks

Dear Nebu,

Well if i give a formula then if i click on the cell that formula will appear in the formula taskbar. So if we put as a macro code it won't appear in the formula taskbar. So do code it for me for both the things.

Regards,

Sonjoe Joseph.

 

[SONJOE JOSEPH]

Hi:

Why you want code for this, the function today() is dynamic , yo do not need a macro for this.

Thanks

Dear Nebu,

SPOT SHEET
----------

Good Morning and how are you? I have added some more things in the Stock Test file. During the week end i was just working to give a statiscial formula to find the ADR ie Average Daily Return of the Daily rerurns in column "C". I tried to give a coding for it but the value is not coming in the cell "O4". The idea of the code is to find the yearly Daily average return starting from 20-May-16 Minus 365 days which will be 21-May15. Using this idea in mind i have given the code but was not successful in it.Here i have used the average function which we have in excel. Do see the code and make changes accordingly so that it will run smothly.

FUT SHEET
---------

Moreover i have named a new sheet called "FUT". The problem here i'm facing is that when i enter the Open, High, Low, Close & Ltp the values of DR,CR & CR-Sq are not coming immediately but its taking some time to come. Even though the values are coming correct its taking some time. Do check and making changes so that this problem is solved.

Awaiting for your feedback. The working files are attached.


Thanking You,

With Regards,

Sonjoe Joseph.

 

[Nebu]

Hi:

Find the attached. Next time give all your requirements at once , do not feed piecemeal like this. This coding could have been done in very few posts.

Thanks

 

[SONJOE JOSEPH]

Hi:

Find the attached. Next time give all your requirements at once , do not feed piecemeal like this. This coding could have been done in very few posts.

Thanks

Dear Nebu,

Thank you just saw your post now. Nebu some of the statistical functions i'm also in the learning process by referring a book. So first i should know then only i can explain it to you. In my next post i will try to learn a few statistical functions faster and will ask four or five at one shot. This attachment i will check it and any doubts i will get back to you.

Thanking you once again for all your help...Good Day to you.

With Regards,

Sonjoe Joseph.

 

[SONJOE JOSEPH]

Dear Nebu,

Good Morning and how are you. Just need your help on the following issues facing on the StockTest and some statistical functions which i learned during the week.

1. In the FUT sheet when i enter the Open, High, Low, Close & LTP the DR, CR, CR-SQ & DV values are not coming instantly. There is some delay in the values being appeared. I need to click on some blank cells then only the values will come in the DR, CR, CR-SQ & DV. Do check the issue.

2. In column PP the calculation will be:-

=average(open,high,low,ltp) which should be dynamic

3. In column BE the calculation will be:-

=(LTP+(LTP*DV*0.236)) which should be dynamic

The working file is attached along with this message.

Regards,

Sonjoe Joseph.

 

[Nebu]

Hi:

Find the attached.

Thanks

 

[SONJOE JOSEPH]

Hi:

Find the attached.

Thanks

Dear Nebu,

Thank you for your attached file. I checked it its working perfectly fine. The same thing i tried to do myself but still i'm facing problems in the "FUT" sheet when i enter the open,high,low,close & ltp the values of DR, CR, CR-SQ & DV values are not coming instantly. I don't know the same code only i gave but in my case its not working. Can you just check the working file what is wrong with the code. Just tell me what went wrong?

You have been a great help to me ...for each question i ask learning lots of new things from you and at the same time learning lots of statistical functions.

Regards,

Sonjoe Joseph.

 

[Nebu]

Hi:

The event you selected for FUT sheet is wrong . I have selected Worksheet_Change event, whereas, you have selected Selection_Change event for FUT, Change the event from Worksheet_SelectionChange to Worksheet_Change

Thanks

 

[SONJOE JOSEPH]

Thank You Nebu....oh this was my mistake....Didn't quite notice that fine. Just looking at some more statistical calculation and will get back to you. Good Day.

 

[SONJOE JOSEPH]

Hi:

The event you selected for FUT sheet is wrong . I have selected Worksheet_Change event, whereas, you have selected Selection_Change event for FUT, Change the event from Worksheet_SelectionChange to Worksheet_Change

Thanks

Dear Nebu,

Good Morning and how are you? Long time i'm messaging u since i was stuck up in some work and was learning some statistical concepts. Well Nebu just have a doubt we have found the DAR (O4) & DSD (O8). Using this same formula i want to find the DAR & DSD of the Future Nifty were the datas are in the FUT sheet. Since this is in different sheet how to apply the formula. Attaching the working file for your working.

Thanking You,

With Regards,

Sonjoe Joseph

 

[Nebu]

Hi:

Find the attached.

Thanks

 

[SONJOE JOSEPH]

Hi:

Find the attached.

Thanks

Thank You Nebu....just checking your code....

 

[SONJOE JOSEPH]

Hi:

Find the attached.

Thanks

Dear Nebu,

How are you. I have added some more concepts in the stocktest file during my learning process. I have added another sheet named "LIVE" were in this sheet i have linked my trading terminal to excel. As soon as the trading terminal is connected to the "LIVE" sheet will be showing the live prices for the symbols i have linked. During this process i was facing some issues which i would like to bring to your notice so that the problem will be solved.

1. In the SPOT sheet you will see the SPOT CMP (S7) & Future CMP (S15). Right now i have entered this manually then only the calculations will come from Z to AE were you can see some numbers which i have given some calculations using vba code. The idea is i need to connect S7 & S15 in the SPOT sheet to the LIVE sheet ie in C1 & C3 (LTP). I hope you got my idea whatever the price changes are happening in the LIVE sheet C1 & C3(LTP) this will get reflected in the SPOT sheet S7 & S15. Once this problem is solved then only i think prices will change in the SPOT sheet from Z to AE.

2. As soon as i run the macro by F5 there are lots of "zeroes" coming in the "LIVE" sheet which i also need to get rid off. I don't know how this is coming do check my code. I have not done any code in Sheet3 (LIVE) then how come this values are coming. Do solve the issue. Attaching some pictures of this problem then only u will be able to see it.

3. How to run the macro without clicking the F5 button. My idea is as soon as i open the Stocktest file my macro has to start automatically.

Awaiting for your help for the above mentioned issues and attaching some pictures and the working file.

Thanking You,

With Regards,

Sonjoe Joseph.

 

[Nebu]

Hi:

Your first question you can simply give cell reference to get the values in cell SPOT CMP (S7) & Future CMP (S15).

Your second question , I am not sure why zeroes are populating on your live sheet. I guess it is something to do with your link to the live data. The coding has nothing to do with it.

Your third question , why are you using F5 to run the code,the macro is designed to run on cell update on sheet SPOT or FUT.

Thanks

 

[SONJOE JOSEPH]

Hi:

Your first question you can simply give cell reference to get the values in cell SPOT CMP (S7) & Future CMP (S15).

Your second question , I am not sure why zeroes are populating on your live sheet. I guess it is something to do with your link to the live data. The coding has nothing to do with it.

Your third question , why are you using F5 to run the code,the macro is designed to run on cell update on sheet SPOT or FUT.

Thanks

Dear Nebu,

I also tried cell reference but the values are not getting updated live from Y to AE in OED, 30 Days & YEAR in SPOT as well as in FUT in the "SPOT" Sheet. Only when i click F5 then only the values changes from Y to AE but once i click F5 the FUT NIFTY from the cells O17 to O19 also O21 to O23 value changes which is not accordingly to the formula which i have given in the vba code ie all the values are wrong which i have marked in the brown area.

I'm attaching some pictures and working file for you reference. Please go through it and just solve the issue so that all things work smoothly.

Thanking You,

With Regards,

Sonjoe Joseph.

 

[Nebu]

Hi:

I guess the error is because of live updates the only thing you can do is to paste special the values from LIVE tab as values you can include this line in your code

sheets("LIVE").range("C1").copy
Sheets("SOPT").range("S7").PasteSpecial Xlvalues
application.cutcopymode=false

Thanks

 

[SONJOE JOSEPH]

Hi:

I guess the error is because of live updates the only thing you can do is to paste special the values from LIVE tab as values you can include this line in your code

sheets("LIVE").range("C1").copy
Sheets("SOPT").range("S7").PasteSpecial Xlvalues
application.cutcopymode=false

Thanks

Dear Nebu,

My pc is out of order will get back to u once my pc is fine. Thank you for your prompt reply.

Regards,

Sonjoe Joseph.