Need help with co-relation (not sure if it called that)

[virtualtechie17]

Basically there are two values , both stock

if one is valued lets say 6170 at a point in time and at that same point in time other stock (that moves based on the price of the first stock) is priced at £114

can someone provide me with an excel formula , so if i enter the price of first stock at any point in time lets say 6300, it would calculate the value of the second stock at that point in time..

hope it makes sense

 

[HallsofIvy]

There is no such formula because there are infinitely many ways two "values" can be related.

For example it might be that one value is the other plus or minus some fixed number. 6170- 114= 6058. That is, 114 is 6170 minus 6058. If the second number is always the first number minus 6058 then when the first number is 6300, the second would be 6300- 6058= 242. Or it might be that the second number is the first number divided by some fixed number. 6170/114= 54.1228 (to four decimal places) so 114= 6170/54.1228. If the second number is always the first number divided by 54.1228 then when the first number is 6300, the second would be 6300/54.1228= 116.4020 (again to four decimal places).

But those are big "if"s! Do you have any reason to believe that the second number is determined by the first number in some specific way or determined by the first number at all? If these "stocks" you are talking about are in a "stock market" then there is, generally, no reason to believe one is determined by another in any way!

 

[virtualtechie17]

yes the second number if determined by first number - basically the first number is the price of FTSE (at the time) , i am taking nows i.e. 6124
and the second 'instrument' which basically another exchange traded fun gives you 3x (postiive or negative return) based on ftse price movment, so if price of ftse goes up by 1% its price would go up by 3% , or if ftse goes down by 1% , it price would go down by 3%

the current price of the second 'instrument' at the time of writing is this - £111.63

i just need a formula that i can use to calculate the price on 2nd instrument any point in time , based on the price of 1st instuement, as i will always clearly have price of 1st (well i will always have the price of second as well !)

what i need is, lets say i want to take a trade when so lets say price of ftse is 6500, what would be the price of the 2nd instrument at that time.. ??

does it make sense?

infact i need the formual for many other instuments as well, so if put the first price or something in, or first and second price, it would then calculate what the value of 2nd instument would be based on 1st (at any point in time, apart from the current prices, as i will always have that)

another example is value of gold is at the time of writing - 1762 , there is instrument that is based on gold price and it gives 3 times the return, negative or positive , so if gold move 1% , the price of 2nd instument goes up by 3% , if gold goes down by 1% , price of 2nd instrument goes down by 3%, the price of that instrument at the time of writing is - £33.97

so what i want to do is if i want to enter the trade at when gold price is 1800, i want to be able to get the value of the 2nd instrument within second (using the excel calculator that i develop ((with help)) , rather than having to manually calculate

 

[Steven G]

Let x = 1st price. Let y = 2nd price. Let c = the change in price 1 in percent. Let new y = the value of stock 2 after stock 1 changes

Then new y = y + cy = (1+c)y, given that price of stock 1 went up (or down) by c