• 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.

Calculation of daily returns of an asset class

SONJOE JOSEPH

SONJOE JOSEPH

Member
Dear All,

I'm not a vba expert so just looking for your help to find the daily returns of a particular asset class using vba code. The formula for calculating the daily returns is as follows:-

DR = (Today Closing Price - Yesterdays Closing Price ) / Yesterdays Closing Price

The idea is everyday i will be adding the date and closing price manually and automatically DR (Daily Return) calculation has to come automatically.

Can anybody help me out.

Thanking You,

With Regards,

Sonjoe Joseph

A sample file is attached without codes.
 

Attachments

  • Stock Test01.xlsm
    12.4 KB · Views: 5
Last edited by a moderator:
Sonjoe

Firstly, Welcome to the Chandoo.org Forums

In cell C4 type in: =(B4-B3)/B3
Now copy this down

Every day as you enter the new data simply go to the cell below the last formula in column C and press Ctrl+D

No VBA required
 
Dear Joseph
Welcome to :awesome: group
I want to know , do you want to enter Date and Closing price in VB Input Boxes ?
 
I have added small VBA code, if you need that way you can use it, Click on Input values button. also changed @Hui formula little bit in C column.
 

Attachments

  • Stock Test01.xlsm
    16.7 KB · Views: 4
Ashhu said:
I have added small VBA code, if you need that way you can use it, Click on Input values button. also changed @Hui formula little bit in C column.
Click to expand...

Thank You Ashu. It's working perfectly fine. But how to drag the button "Input Values". I tried dragging the "Input value button" in the design mode but its not getting dragged. Do help me out.
 
Hui said:
Sonjoe

Firstly, Welcome to the Chandoo.org Forums

In cell C4 type in: =(B4-B3)/B3
Now copy this down

Every day as you enter the new data simply go to the cell below the last formula in column C and press Ctrl+D

No VBA required
Click to expand...

This method already i know....I want to add some more features later. Anyway thank you for your prompt reply.
 
Ashhu said:
I have added small VBA code, if you need that way you can use it, Click on Input values button. also changed @Hui formula little bit in C column.
Click to expand...

I tried doing myself in a separate file but i didn't understand how did you do it. Can you explain it for me. How did you add the "Input Values" button and how did you apply the formula to the other cells as soon as we enter the date and closing price. Awaiting for your feedback
 
SONJOE JOSEPH said:
Thank You Ashu. It's working perfectly fine. But how to drag the button "Input Values". I tried dragging the "Input value button" in the design mode but its not getting dragged. Do help me out.
Click to expand...
Dear Joe
to move Input Values button, point cursor on the button and Right click mouse button. it will enable to move, you can be able to see thick dotted border. Hold to that border and drag to destination. See attached image.
I hope its clear.

-Ashwin
Another way to say thanks is to hit "Like" button :)
Click to expand...
 

Attachments

  • move.jpg
    move.jpg
    54.4 KB · Views: 7
SONJOE JOSEPH said:
I tried doing myself in a separate file but i didn't understand how did you do it. Can you explain it for me. How did you add the "Input Values" button and how did you apply the formula to the other cells as soon as we enter the date and closing price. Awaiting for your feedback
Click to expand...
Dear Joe,
I appreciate your enthusiasm to learn and undoubtedly i can say you are right place. thanks to @r1c1 @Hui .

to answer your question,
"Input values" button is associated with simple 2 line VB code [Press ALT F11] which runs when you click on the button.
you can insert button from developer tab. (see image)
IfError Formula in column C is already entered till C47, along with If formula.

if you want to understand how these frmula works, you simple type function name(ex:IfErrror) in search page on top right corner of this page, forum will guide you with sytax and its application.

-Ashwin

Another way to say thanks is to hit "Like" button :)
Click to expand...
 

Attachments

  • Input button.jpg
    Input button.jpg
    68.9 KB · Views: 4
Ashhu said:
Dear Joe
to move Input Values button, point cursor on the button and Right click mouse button. it will enable to move, you can be able to see thick dotted border. Hold to that border and drag to destination. See attached image.
I hope its clear.

-Ashwin
Click to expand...


Ok now i'm able to move the the button. But one more thing which i have noticed is that the formula which you have given is not dynamic in nature. That is now your formula will work till row 47. Once i start entering from row 48 the date and closing price your formula won't work. So i want to make the formula dynamic in nature which means it it should be infinite.
 
SONJOE JOSEPH said:
Ok now i'm able to move the the button. But one more thing which i have noticed is that the formula which you have given is not dynamic in nature. That is now your formula will work till row 47. Once i start entering from row 48 the date and closing price your formula won't work. So i want to make the formula dynamic in nature which means it it should be infinite.
Click to expand...
Dear Joe,
Please find updated sheet to suit your requirement.
 

Attachments

  • Stock Test01.xlsm
    16.8 KB · Views: 4
SONJOE JOSEPH said:
Thank you Ashu for the great help...I will look into detail once i reach home.
Click to expand...

Dear Ashu,

Good Morning. How are you?. Well i have checked your file and its working perfectly fine. But there is a slight problem when i click the cancel button its shows a debug error message. Say suppose if i enter the date or closing price wrongly and if i need to renter again. When such an error occurs normally any user will press the cancel button and then starting to reenter it again.

Even i tried myself by creating a new file and just had a warm up with your coding to learn myself. But when i tried it showing lots of bugs and its not working perfectly fine. Even while i enter the date the formatting is also not coming perfectly fine when i enter through the input box. If i don't enter the date through the input box and type the date straightly to the date column the formatting is well intact.

Just sending my working file along with this message. Just go through this and just correct were i have gone wrong and please do the necessary correction.

Thanking You,

With Regards,
Sonjoe Joseph
 

Attachments

  • DR Test.xlsm
    18.1 KB · Views: 2
Last edited by a moderator:
SONJOE JOSEPH said:
Ashhu i have send a message in the morning also an attached file. Just awaiting for your reply and the updated file with the corrections.
Click to expand...
Dear Joe
Sorry , I am little tied up with company audits till tomorrow, if its fine for you i can look it latest by Wednesday.

The simple code what i wrote is just for data entry, there are lot of improvements we can do the sheet like, if Date entered in wrong format user will be alerted ...

I will help if you really want to go ahead with VB automation to excel file.

Check this file as sample.
http://forum.chandoo.org/threads/to-do-list-vb-interface.28973/
 
Ashhu said:
Dear Joe
Sorry , I am little tied up with company audits till tomorrow, if its fine for you i can look it latest by Wednesday.

The simple code what i wrote is just for data entry, there are lot of improvements we can do the sheet like, if Date entered in wrong format user will be alerted ...

I will help if you really want to go ahead with VB automation to excel file.

Check this file as sample.
http://forum.chandoo.org/threads/to-do-list-vb-interface.28973/
Click to expand...
Hi Joe
please find updated code.
 

Attachments

  • DR Test.xlsm
    18.5 KB · Views: 2
Ashhu said:
Hi Joe
please find updated code.
Click to expand...
Thank Ashhu for the updated file. I checked and the updated file is working perfectly fine only thing the date format is not coming correct. Well i will try to do myself and if i'm having any issues i will get back to you. I hope i'm not at all disturbing you with my doubts. Thank You for the great help and being my teacher in Excel VB macros.
 
Ashhu said:
Hi Joe
please find updated code.
Click to expand...

Dear Ashhu,

Well i tried learning your code by practicing it on another file but still my ride is not so smooth getting lot of errors. Just would like to bring to your notice some key points which are listed below:-

1. First thing in the DR Test file from the developer tab i will insert an Active X control Command button. Once this is created i will right click on the mouse and go to the properties of the command button. There i will change the Caption & Name to "Update".
2. Now when i double click on the Command button it will happen like this:

Private Sub Update_Click()

This is the area were i want to add the code.

End Sub

3. Now coming to the code when i click the update button. First it should ask me to enter the date. Suppose by mistake if i enter a wrong date when i click the cancel button it has to clear the wrong date which i have entered. Also give a validation message that "The date format is wrong" in the date column
4. After this i will be entering the closing price. In case the closing price i entered is wrong then i will be again clicking the update button to renter the date and closing price again.
5. Once the closing price is entered correctly again then the DR (Daily Return) should be calculated automatically and dynamic. Hope now you got my idea.

Keep the code as simple as possible so that beginners like us can understand.

So have a working of two files one with all the issues mentioned above and
second without input box were i will be entering the date,closing price and the daily returns has to be calculated automatically in a dynamic way.

Hope you have understood.

This two files are attached below one with Command button and one without command button. Do code it and make it simple.

Sonjoe Joseph.
 
Ashhu said:
Dear Joe,
I am sorry i missed your previous message. I will help you out as soon as i can. please bear with me.
Click to expand...

Take your time Ashu..I just reminded you. Thank you for the great help and having the patience to hear my query. You are a real good friend of mine.
 
Dear Joe
I am attaching the update file.
Let me answer your query one by one.
1. you are right,
2. you have to enter the code between the first and last default line.
3. now the code will identify the format and give warning message for wrong format. if you click on cancel, there is message box to close or to back to enter date message box.
4. if you click on cancel, there is message box to close or to back to enter date message box.
5. remain same as before.

the code is not simple looks complicated until you get use to it, i have put instruction in code before each execution (green color) for you to understand what it does.

let me know if need any help further. have good night.
 

Attachments

  • DR Test(1).xlsm
    20.1 KB · Views: 6
Thanks Ashhu for the updated file. I will try to do myself with another file and if i'm having any doubts i will come back to you. Good day
 
You must log in or register to reply here.
Back
Top