• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Nebu said:
Hi:

The error is basically because you have not declared the variable.
either you declare the variable using dim statement or use (dr&) , the code will work.

Thanks
Click to expand...

Dear Nebu,

Still i'm getting the same error. Don't know what's happening.
 

Attachments

  • err2.png
    err2.png
    228.9 KB · Views: 1
  • Stock Test01.xlsm
    16.2 KB · Views: 2
Hi:

Find the attached its working fine now.

Thanks
 

Attachments

  • Stock Test01.xlsm
    16.4 KB · Views: 1
Nebu said:
Hi:

The error is basically because you have not declared the variable.
either you declare the variable using dim statement or use (dr&) , the code will work.

Thanks
Click to expand...

Dear Nebu,

Again thinks are not going fine for me same error coming again everytime when i type the same code everytime by myself. On the other hand if i simply copy and paste your code again it will work. But if i type your code it will not work. Why this is happening Nebu. Just check.
 

Attachments

  • err.png
    err.png
    282.2 KB · Views: 2
  • Stock Test01.xlsm
    13.7 KB · Views: 2
SONJOE JOSEPH said:
Ok let me check....will get back to you in case of a problem. Hope i'm not disturbing you with my frequent queries.
Click to expand...

Nebu said:
Hi:

In Xlup, the "U" should be in caps, you are typing it in small case.

Thanks
Click to expand...

Hello Nebu,

Now its working perfectly fine. Thank You for clearing my doubt. Good Day. Just one more question as a beginner myself how can i become a master in vba macro coding. Are there any good sites or books to know more about the macro vba coding aspects for beginners like me.

Regards,

Sonjoe Joseph
 
Hi:

There are lot of info available over the web. I do not have any specific website on mind.Also check out more of this website, there are lots of information available within this website as well.

Thanks
 
Nebu said:
Hi:

There are lot of info available over the web. I do not have any specific website on mind.Also check out more of this website, there are lots of information available within this website as well.

Thanks
Click to expand...

Dear Nebu,

Thanks for the information. In the meanwhile i have added in more header in column D as "LN". The formula for LN=C4/C3 even we have a function in excel called LN. Using the same logic of yours i tried to give a code. But its not working fine for me. My idea here is as soon as enter the date and closing price the daily return and inverse log has to be calculated automatically. Right now daily return is working perfectly fine, also want the LN to also working properly like daily return. Just see my code on sheet1 as well as on module and tell me why LN is not calculated automatically. I used the same logic of yours but still its not coming.
 

Attachments

  • Stock Test01.xlsm
    15.7 KB · Views: 1
Nebu said:
Hi:

Find the attached.

Thanks
Click to expand...

Dear Nebu,

I'm facing some issues which are listed below:-

1. The Ln formula which we have given is not coming correct.In excel we have an inbuilt function called Ln were i have given the formula as =Ln(B4/B3) were the ans is 0.0423. The present formula which we have given are giving the same values of DR ie Daily returns. How can i use the Ln function as VBA code.

2. Yesterday when i tried copying the dates and closing prices from my other excel file to the present excel file which we are working on the daily reurns formula for the dates of 3rd and 4th Jan are not calculated. So there is some issue while we copy values from one excel file to another which the issue has to be fixed.

Regards,

Sonjoe Joseph.
 

Attachments

  • Stock Test01 (1).xlsm
    19 KB · Views: 1
Nebu said:
Hi:

Find the attached.

Thanks
Click to expand...

Dear Nebu,

Thanks and now the Ln function is working perfectly fine. Now the only thing is the 2nd part of the solution is not yet solved. I need to copy and paste 4 year historical data starting from 2012 to 2016 from one of my excel files to the present working file Stock Test01. Again i tried doing a copy and paste with 5 more additional data but still its not working perfectly fine. Again blanks are cropping in and need your guidance to solve this issue.

Attaching the screenshot and working file for your reference.

Thanking You,

Regards,

Sonjoe Joseph.
 

Attachments

  • error.jpg
    error.jpg
    402.4 KB · Views: 1
  • Stock Test01 (1) (1).xlsm
    19.4 KB · Views: 0
Hi:
I have coded the macro to update once you enter values on a daily basis, which was your original requirement. If you want a bulk update copy and construct the formula manually and drag it and copy and paste as values. Use the macro only to calculate when you enter fresh values into your data, not for historic values.

Thanks
 
Nebu said:
Hi:
I have coded the macro to update once you enter values on a daily basis, which was your original requirement. If you want a bulk update copy and construct the formula manually and drag it and copy and paste as values. Use the macro only to calculate when you enter fresh values into your data, not for historic values.

Thanks
Click to expand...

Dear Nebu,

But is there a way while copying and pasting works since it will be damn good when large amounts of data can be offloaded at one short and i will be getting all the formula on one go. I don't mind if you know please do change the code. You are free to do it. Would like to know your opinion.

I can even do one thing first i will be copying all the datas from my excel to this working excel file. Once done can we get all the formulas at one shot ie Daily returns, LN etc.

Waiting for your feedback. Do tell me if i need to copy all the historical prices.

Regards,

Sonjoe Joseph.
 
Nebu said:
Hi:
I have coded the macro to update once you enter values on a daily basis, which was your original requirement. If you want a bulk update copy and construct the formula manually and drag it and copy and paste as values. Use the macro only to calculate when you enter fresh values into your data, not for historic values.

Thanks
Click to expand...

Nebu i have send a message to you...waiting for your suggestion. Are u there online.
 
Hi:

I live in Australia and because of time difference I may not be seeing your message, you have to be bit patient. My opinion is that you do not need separate code if your bulk update is going to be one time. You can achieve this manually entering the formula and dragging it down (Once done you can paste special it as values). You can use the code I have given before for your daily update.

Thanks
 
Nebu said:
Hi:

I live in Australia and because of time difference I may not be seeing your message, you have to be bit patient. My opinion is that you do not need separate code if your bulk update is going to be one time. You can achieve this manually entering the formula and dragging it down (Once done you can paste special it as values). You can use the code I have given before for your daily update.

Thanks
Click to expand...

Hai Nebu,

Ok Understood i will do it manually as per your advice. But didn't not understand "paste special it as values". Nebu i'm not so expert in excel just learning new concepts from professional guys like you. So don't get bored if i ask doubts ok. That's the only way i feel can be a master like you in excel by asking doubts. You are a great teacher.

Regards,

Sonjoe Joseph
 
SONJOE JOSEPH said:
OK understood and will do accordingly. Thanks for clearing my doubt.
Click to expand...

Dear Nebu,

Good Morning and how are you? I have updated the Stock Test01 file and have included a column for banknifty. As usual when i enter the date and closing prices for Nifty as well as banknifty all the DR, LN, LN-SQ has to be calculated automatically. Using the same logic of yours i have given the code for banknifty but its not working at all. Just see when i entered the 13th May-16 data the Nifty calculations are coming perfectly fine but Banknifty calculations are just blank. Attaching the screen shot and the working file.

Just awaiting to resolve this issue.

Regards,

Sonjoe Joseph.
 

Attachments

  • err.png
    err.png
    309.8 KB · Views: 1
SONJOE JOSEPH said:
Dear Nebu,

Good Morning and how are you? I have updated the Stock Test01 file and have included a column for banknifty. As usual when i enter the date and closing prices for Nifty as well as banknifty all the DR, LN, LN-SQ has to be calculated automatically. Using the same logic of yours i have given the code for banknifty but its not working at all. Just see when i entered the 13th May-16 data the Nifty calculations are coming perfectly fine but Banknifty calculations are just blank. Attaching the screen shot and the working file.

Just awaiting to resolve this issue.

Regards,

Sonjoe Joseph.
Click to expand...


My working file attached
 

Attachments

  • Stock Test01.xlsm
    262.8 KB · Views: 1
You must log in or register to reply here.
Back
Top