The correlation table is correct, yes, 0 = 1 
The varianse table is acutally a standard deviation table, using the same formula you postedā¦
here is a zip file with all the daily candles used in the calculation(all is just for 2012).
(Sorry for the confucion )
FX Portfolio.zip (76.8 KB)
Ok so iāve correlation table and standard deviation table. thats really good 
Weāve all the data we need, now I need someone very good with excel, to turn my 2 risky assets portfolio into a 26 one xD
Iāll try myself but I dont think i can do it. Only thing I can do is sort up the data in the proper way
While searching the web I found an article by RON SCHELLING related to Forex Portfolio. It concentrate on the Forex market ranking. I hope this can be of use for you guys.
I couldnāt post the link here as I am still new at babypips, however you can simply google for āRON SCHELLING forex portfolioā and youāll get it at top of the search list.
I am following your thread with interest.
Abokwaik
Thx for your post! I saved out Schellingās work: iām gonna read it asap
@Richard: those standard deviations are really small (usually around 2% as u can see) and then we have some spike up to 200-400% with the yen crosses, is this normal? 
The first seems too small, and the second too big! Are you sure about the correct computation of the formula?
Just to give an example:
Ļ= [[(Daily value1 - mean of all 365days)^2+(Daily value2 - mean of all 365days)^2+(Daily value3 - mean of all 365days)^2+ā¦+(Daily value n - mean of all 365days)^2]/364]^1/2
To be honest, I cheated and used the built in standard deviation formula in R, wich just takes a list of prices, and gives me the standard deviationā¦
dvs:
variances$OPEN_V[x] <- round(sd(pair$Open),8)
variances$HIGH_V[x] <-round(sd(pair$High),8)
variances$LOW_V[x] <-round(sd(pair$Low),8)
variances$CLOSE_V[x] <-round(sd(pair$Close),8)
variances$HiLo_V[x] <-round(sd(pair$High-pair$Low),8)btw, dosnt the sd return the standard deviation in pips, not percents, dvs, in the yen pairs this would be MUCH higher? Not really sure though 
Reading with interest. Itās fun watching you guys decipher this.
i think, just like you cryagony, that the expected return is going to be a pain in the a**. actually iām not really sure if this type of analysis does make sense at all in FX because exchange rates are (mostly) mean reverting. thus if you estimate the historical expected return for the last year, this will not be an indicator for the return in the following year. probably quite the opposite. but still interested to see where this is heading
Richard, Iām an idiot 
Youāre right and I omitted to tell you a step, sorry. We need standard deviation in %. So I need you to do a further step.
For each value that you found, I need you to do the further operation:
Ļ%= (Ļ/ aritmetical mean of the pair)x100
Let me know when you got the data
for aritmetical mean i mean (sorry the trick here hih): eg: sum of all the 365 close candles (or highs or lows etc etc)/365. Easy as that
with mean reverting you refer to correlation, right? Check out the table of correlation uploaded by Richard, and youāll notice how this is not so true 
Many many pairs have really small correlation, and THOSE pairs will be the ones forming our portfolio, due to smaller standard deviation (ie risk) and higher reward-to-volatility (Sharpe) ratio. Iāve already spotted some interesting pairs
But thatās one step ahead ^_-
Right now, Iām desperatly in need of someone with good excel skills !!
Thx for your all interest and help, all ideas welcome !
Thank you mate! Feel free to say your own ideas/comments
:59:
That would probably take 5min when I get home Iāll probably have the data ready in a couple of hours
Thx for highlighting it I checked it out partly, but it seems the topics are quiet different
Iāll read it more in details tho!
I think I got itā¦
here is the function I used:
temp_mean <-mean(pair$Close)
temp_sd <- round(sd(pair$Close),8)
variances$Custom[x] <- (temp_sd / temp_mean)*100
and here is the results, check out the last collumn
"SYMBOL" "OPEN_V" "HIGH_V" "LOW_V" "CLOSE_V" "HiLo_V" "CUSTOM"
"1" "USDJPY" 1.99551948 2.02524243 1.97725762 2.01813888 0.22900091 2.52996440828512
"2" "GBPUSD" 0.02406864 0.02358335 0.02440603 0.02406627 0.00353219 1.51888811506479
"3" "USDCHF" 0.02279731 0.022959 0.02248253 0.02281974 0.00287169 2.43314089421497
"4" "EURCHF" 0.00416146 0.00446042 0.00382101 0.00410235 0.00109243 0.340502798418331
"5" "AUDUSD" 0.02293862 0.02273053 0.02305876 0.02294764 0.00308224 2.21636564903242
"6" "NZDCAD" 0.01406935 0.01389446 0.01411359 0.01403152 0.00189097 1.73434625677544
"7" "NZDJPY" 2.62183225 2.60890864 2.61685351 2.61900178 0.26423691 4.05492923397103
"8" "AUDNZD" 0.01559149 0.01544954 0.01550566 0.01551839 0.0020174 1.21337544455136
"9" "EURNZD" 0.03706561 0.03703462 0.03674803 0.03683832 0.00455894 2.32024197753652
"10" "GBPCAD" 0.0152808 0.01517172 0.01534372 0.01547403 0.00337846 0.977272947360717
"11" "AUDJPY" 2.87957582 2.85702286 2.89310788 2.88522723 0.32221539 3.49343082929421
"12" "CADJPY" 2.54393807 2.54623343 2.54042713 2.5516135 0.29893528 3.19616911877286
"13" "EURGBP" 0.01701436 0.01700445 0.01686603 0.01693532 0.00170992 2.08847870300841
"14" "CHFJPY" 3.55862695 3.5714713 3.56413704 3.57975297 0.33120241 4.20627809176899
"15" "USDCAD" 0.01496253 0.01517522 0.01468295 0.01489874 0.00229493 1.49062277795358
"16" "NZDUSD" 0.02063687 0.02040645 0.02082096 0.02058687 0.00270979 2.54260481147362
"17" "GBPCHF" 0.02932171 0.02912953 0.02926295 0.02921677 0.00316106 1.9663445200591
"18" "EURAUD" 0.03071101 0.03061191 0.03076995 0.03074612 0.00315654 2.47642794928325
"19" "EURJPY" 4.35175574 4.36629993 4.3670133 4.38310134 0.40235344 4.27320865736152
"20" "GBPJPY" 4.31350075 4.33224502 4.31607699 4.34099584 0.45781902 3.43376672261982
"21" "EURCAD" 0.02846522 0.02831166 0.02832668 0.02833444 0.00330857 2.20611807793358
"22" "AUDCAD" 0.01865944 0.01844487 0.01864841 0.01856718 0.00207176 1.79453862766657
"23" "CADCHF" 0.02025574 0.02023068 0.02010592 0.02018399 0.0024827 2.15092428123805
"24" "AUDCHF" 0.02405702 0.02420305 0.02385607 0.02409719 0.00252506 2.48215164857277
"25" "NZDCHF" 0.01762084 0.01761221 0.01751543 0.01756306 0.00214523 2.3136605488426
"26" "EURUSD" 0.03277781 0.03255617 0.03286216 0.03282966 0.00385105 2.55449447409255
itās exactly the same data so nop 
can you see anything wron? isnt it (SD / Mean) * 100? wierdā¦
SYMBOL CLOSE_V PercSD
USDJPY 2.01813888 2.52996440828512
GBPUSD 0.02406627 1.51888811506479
USDCHF 0.02281974 2.43314089421497
EURCHF 0.00410235 0.340502798418331
AUDUSD 0.02294764 2.21636564903242
NZDCAD 0.01403152 1.73434625677544
NZDJPY 2.61900178 4.05492923397103
AUDNZD 0.01551839 1.21337544455136
EURNZD 0.03683832 2.32024197753652
GBPCAD 0.01547403 0.977272947360717
AUDJPY 2.88522723 3.49343082929421
CADJPY 2.5516135 3.19616911877286
EURGBP 0.01693532 2.08847870300841
CHFJPY 3.57975297 4.20627809176899
USDCAD 0.01489874 1.49062277795358
NZDUSD 0.02058687 2.54260481147362
GBPCHF 0.02921677 1.9663445200591
EURAUD 0.03074612 2.47642794928325
EURJPY 4.38310134 4.27320865736152
GBPJPY 4.34099584 3.43376672261982
EURCAD 0.02833444 2.20611807793358
AUDCAD 0.01856718 1.79453862766657
CADCHF 0.02018399 2.15092428123805
AUDCHF 0.02409719 2.48215164857277
NZDCHF 0.01756306 2.3136605488426
EURUSD 0.03282966 2.55449447409255
Ah wait! I ahd to watch the last column before! Right! Sorry lol, Itās because I saw the first columns and the numbers were the same. Yes thatās it
I expected way higher standard deviationā¦uhm
Hey Richard, Can you pack those sd% in a txt file like always so that I can paste it easily in excel? and can you attach also the same results using hi-lo instead of close?
Many thx for all your kind help!! We are nearly done racking up datas
here you go:
And here is how all the values are calculated
variances$CLOSE[x] <-round(sd(pair$Close),8)
variances$CLOSE_PCT[x] <-round((sd(pair$Close) / mean(pair$Close)*100),8)
variances$HiLo[x] <-round(sd(pair$High-pair$Low),8)
variances$HiLo_PCT[x] <-round((sd(pair$High-pair$Low) / mean(pair$High-pair$Low))*100,8)
Im interested to see how it turns out Do you have a plan about the Excel schema?