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

How to extract data from a sheet as soon as i click the combo box?

Dear All Excel Experts,

In the working file there are two sheets named "LIVE PRICE" and "BSM". In the "Live Price" sheet as soon as i click the combo box and select any of the items say NIFTY17MAY9200PE it will have to extract the Delta, Gamma, Theta,Vega & Rho values of this particular NIFTY17MAY9200PE from the "BSM" sheet to the "Live Price" sheet were i have entered 4 columns named Delta, Gamma, Theta, Vega & Rho. In this particular case the values are: Delta =-0.07, Gamma = 0.001, Theta = -1.09, Vega = 0.58 & Rho = -0.15.

Please help me how this can be achived. The working file is attached along with this message.

Regards,

Sonjoe Joseph
 

Attachments

  • Price Match.xlsx
    21.8 KB · Views: 6
Hi,

This should be simple enough, although a little more info is required.
From what is visible in the file attached, it seems you need to look out for two things when searching the values: in this case 9200 and PE.
My question then:
1) What should be returned if you selected "NIFTY17MAY9650CE"

I am assuming that PE should return DeltaPO and CE DeltaCO and so on... but need confirmation on that.
Plus, when the number part doesn't have an exact match in "BSM", should it return the highest value smaller than the lookup value or the smallest value higher than the lookup value?

Thanks
 
Hi,

This should be simple enough, although a little more info is required.
From what is visible in the file attached, it seems you need to look out for two things when searching the values: in this case 9200 and PE.
My question then:
1) What should be returned if you selected "NIFTY17MAY9650CE"

I am assuming that PE should return DeltaPO and CE DeltaCO and so on... but need confirmation on that.
Plus, when the number part doesn't have an exact match in "BSM", should it return the highest value smaller than the lookup value or the smallest value higher than the lookup value?

Thanks

Dear Costa,

Thank You for showing me the error, So now i have updated the file and do kindly look into it.

File attached

Regards,

Sonjoe Joseph
 

Attachments

  • Price Match.xlsx
    26.2 KB · Views: 3
Wonderful PCosta i checked its working fine. Let me do myself incase any doubts i will get back to you.

Regards,

Sonjoe Joseph
 
You are welcome ;)

Dear Friend Pcosta,

Even though the formula is working perfectly fine there is a slight bug when copying the formula down. Just find the screen shot were u will find the error from AD47 to AH50. On the up ur not seeing the error since i was just half way mark doing the working based on your formula. Then finally Bosco has advised to me to point the error while copying the formula.

Hope u will get back to me with the updated formula.

Regards,


Sonjoe Joseph.
 

Attachments

  • scr of err.png
    scr of err.png
    324.5 KB · Views: 3
Hi,

Can you upload the file with the error so I can take a look?
Testing here, in the sample provided, it seems to be working:
1.JPG

EDIT: Most likely you made some mistake when adapting the formula (probably something to do with absolute/relative references).
 
Hi,

Can you upload the file with the error so I can take a look?
Testing here, in the sample provided, it seems to be working:
View attachment 41728

EDIT: Most likely you made some mistake when adapting the formula (probably something to do with absolute/relative references).

Dear PCosta,

I did the necessary corrections in the formula and now its working fine. Thank You for ur prompt support.

Regards,

Sonjoe Joseph.
 
I'm glad it is working now :)

Dear PCosta,

I have another doubt. In the attached excel file in "Sheet 1" you have the live prices were it will be updated from the trading terminal. In "Sheet 2" in the symbol list i have selected the selected strike prices and you will find the BR and AR marked in yellow. As soon as i click any strike price say 9100CE the Bid Rate (BR) and (Ask Rate) has to come in the respective rows and here the updation of the price should not take place when i select any strike price from the symbol list.

Can this be done Pcosta. Do help me out in solving this problem.

Thanking You,

Regards,

Sonjoe Joseph.
 
Hi,

There must be something wrong with the file you uploaded... this is all I get:
1.JPG

Is it supposed to be like this?
 
Hi,

There must be something wrong with the file you uploaded... this is all I get:
View attachment 41871

Is it supposed to be like this?

Dear PCosta,

I'm just attaching another file this is just a copy and paste data from my trading terminal. The Sheet1 prices will change as per the trading terminal. In Sheet 2 i have added 3 combo boxes. As soon as i do the selection in the combo boxes the Bid Rate (BR) and Ask Rate (AR) for the respective strike prices has to come in the related fields. Once the selection is done the price updation should not take place in the respective fields even though the live prices will get updated in sheet 1 from the trading terminal.

Please help me to solve this query.

Regards,

Sonjoe Joseph.
 

Attachments

  • PC.xlsx
    15.1 KB · Views: 3
Hi,

I had to make a couple of changes in order to make it work.
Once you select a value from on of the drop-down menus, the respective fields highlighted in yellow will have their formulas converted to values. Any future update will no longer affect these.

I'm not sure this is exactly what you want but give it a go a let me know.
 

Attachments

  • PC.xlsm
    20.1 KB · Views: 8
Hi,

I had to make a couple of changes in order to make it work.
Once you select a value from on of the drop-down menus, the respective fields highlighted in yellow will have their formulas converted to values. Any future update will no longer affect these.

I'm not sure this is exactly what you want but give it a go a let me know.

Thank you Pcosta for ur prompt response. I will check it and will further update any changes to be made.

Regards,

Sonjoe Joseph
 
Hi,

I had to make a couple of changes in order to make it work.
Once you select a value from on of the drop-down menus, the respective fields highlighted in yellow will have their formulas converted to values. Any future update will no longer affect these.

I'm not sure this is exactly what you want but give it a go a let me know.

Dear PCosta,

Your formula seems to be working fine without a combo box. The main intention of doing all this things is to reduce typing and at the same time i would like to take note of the effect of pricing. So taking this point forward on "Sheet 2" i have added a combo box and the respective cells from C8 to C11 have been lined to the respective cells in B8 to B11. You can see the no's which are marked in yellow. Can this no's can be converted into text. Another idea is when i click the combo box the same text should come in C8 to C11. Once this is done...then when i apply your formula it will come correct.

Just waiting your feedback on this issue and working file attached along with this message.

Regards,

Sonjoe Joseph
 

Attachments

  • PC.xlsm
    21.9 KB · Views: 4
Hi,

Your formula seems to be working fine without a combo box

If you look closely you will notice that, although I didn't use a Combo Box "per se", there is validation in B3:B5 that fetch the data from the source in "Sheet1".
This means you can select the entry just as you do with your Combo Box, you don't need to type anything here.

I did this because you needed the formula to be converted to values so it didn't update if the source values were to change in Sheet1. For this I wrote a Worksheet_Change event that does that when you select something from the drop-down menus. The thing is that this will only work with the validation as the event only occurs when you change the cells manually, which doesn't happen when you use the Combo Box.

The formula isn't the problem here... the problem is making it so it doesn't update after pulling the values from Sheet1.
 
Hi,



If you look closely you will notice that, although I didn't use a Combo Box "per se", there is validation in B3:B5 that fetch the data from the source in "Sheet1".
This means you can select the entry just as you do with your Combo Box, you don't need to type anything here.

I did this because you needed the formula to be converted to values so it didn't update if the source values were to change in Sheet1. For this I wrote a Worksheet_Change event that does that when you select something from the drop-down menus. The thing is that this will only work with the validation as the event only occurs when you change the cells manually, which doesn't happen when you use the Combo Box.

The formula isn't the problem here... the problem is making it so it doesn't update after pulling the values from Sheet1.

Dear PCosta,

Just tell me i want to change the strike price of the B3 cell to NIFTY17MAY9300PE. I'm not able to find any Combo Box in ur file attached. Just see the screen shot. If there was a combo box i would have just clicked on it. I have seen the vba code given in the worksheet change. Will this code affect my other sheets that too i want to know?

Regards,

Sonjoe Joseph.
 

Attachments

  • scr.png
    scr.png
    252.1 KB · Views: 4
Dear PCosta,

Just tell me i want to change the strike price of the B3 cell to NIFTY17MAY9300PE. I'm not able to find any Combo Box in ur file attached. Just see the screen shot. If there was a combo box i would have just clicked on it. I have seen the vba code given in the worksheet change. Will this code affect my other sheets that too i want to know?

Regards,

Sonjoe Joseph.
Wait, that is odd... you should have this:
1.gif

Not sure what is going on :confused:
Please double check if Data Validation isn't working on your end (as above).
 
Hi,

What version of Excel are you using?
I never came across a situation like this, and I can't seem to find a similar problem online, much less a fix for it o_O

At this point I'm just guessing but perhaps there is something wrong with your installation or some settings I'm not aware of.

Is it possible for you to try the file in a different machine and see if it is working?
 
Hi,

What version of Excel are you using?
I never came across a situation like this, and I can't seem to find a similar problem online, much less a fix for it o_O

At this point I'm just guessing but perhaps there is something wrong with your installation or some settings I'm not aware of.

Is it possible for you to try the file in a different machine and see if it is working?

I'm using MS Office Excel 2007. I'm using a corrupted version of windows and office. Hope there is no fix for this right. Do u have teamviewer so that u can directly check.

Regards,

Sonjoe Joseph.
 
Back
Top