Before you begin reading, I want to say thank you for helping, or attempting to help me. I really appreciate any help you can give me! Also, warning: Wall of Text approaching fast.
I've been informed that I am misusing the term Confidence Interval. I have been told that what I am actually looking for is the "Prediction Interval". I'm not sure though as I know my coworker who assigned me this task said Confidence Interval.
- Quantify the variability of exchange rates between system #1 and system #2
- Determine bestfit distribution Find
- 95% confidence interval
Please note: I am doing this in excel, so I will be using excel functions to calculate stdev ect. But if you aren't familiar with excel you can still be of help to me! The concepts are what I need help with!
Here an explanation of the data I was given and what I have done so far: System #1 and #2 BOTH have two lists of 48 numbers. One list being the closing market price in USD and the other list the closing market price in the native currency. So I have "Sys#1 USD", "Sys#1 Native", "Sys#2 USD" and "Sys#2 Native" columns of 48 values each. System #1 is the actual closing mkt price, while System #2 is the one we are testing to see how much it differs from the correct values.
(1.) I found the exchange rate to the dollar for each system by simply dividing Native/USD for the corresponding system.
(#1Native/#1USD) = (#1Native/#1 $1 USD) and (#2Native/#2USD) = (#2Native/#2 $1 USD) `$`
(2.) I then found the percent error of the foreign exchange rates of System 2 compared to System 1.
[ ( (abs[(#2Native/#2 $ 1USD) - (#1Native/ #1 $ 1USD)]) / (#1Native/ #1 $1USD) ] * 100
(3.) I proceeded to find the standard deviation and mean using the simple functions excel comes equipped with. Excel functions below.
(4.) I was informed that using the =CONFIDENCE function in excel was actually NOT what I want because it calculated the CI with the true mean of all future data, and I do not know the true mean value of all future data, only of my sample of 48 days. I was told to use the =NORMINV(probability,mean,standard_dev) function by my coworker. To my understanding, this method "fits a normal distribution to the data and then makes a prediction assuming that this fit is correct." I'm not sure if my data is a normal distribution, so do not know if I can use =NORMINV?
So basically, how do I calculate a 95% confidence interval of this data and determine the best fit distribution? Should I be using =NORMINV?
Thank you so much for your help!