Find solution for formula given historical data of Mine Stock Price and the two metals spot price

[Rx_]

Can't figure out how to chart this. Here are a few dates. Followed by an Investment Account.
Then the spot price of two metals.
The Account represents the total value of over a dozen mining stocks.
Some stocks are pure play for one metal. Some have a 30% 70% mix of both metals.

We are trying to do two things.
1. The perception is that the value of the mines is going up in relation to the metals.
2. Evolve a crude formula that could show "what if"
e.g. if Com1 went up 25% and Com2 went up 35%

If one of your tools for sale include something for this, please let me know that too.


Date Acct Com1 Com2
1/1/14 65,000 1,194 19.81
2/17/14 73,253 1,330 21.94
2/19/14 74,148 1,322 22.06
2/23/14 73,492 1,327 21.95
3/9/14 70,045 1,339 20.89
3/13/14 70,752 1,367 21.32
3/30/14 62,198 1,294 19.82
4/5/14 62,724 1,302 19.96
4/12/14 61,738 1,318 19.96
4/08/14 63,023 1,309 20.06
6/11/14 60,825 1,263 19.27
6/27/14 70,594 1,318 21.03
6/30/14 71,891 1,328 21.06
7/7/14 70,519 1,320 21.05
7/9/14 73,888 1,326 21.10
7/10/14 72,968 1,335 21.42
7/12/14 74,865 1,339 21.45
8/10/14 73,021 1,309 19.91
8/17/14 71,425 1,304 19.55
9/13/14 64,673 1,129 18.61

 

[hohlick]

I recommend you to start with data changes analisys. You can calculate correlation between Acct changes and each of commodities changes:
[TABLE="class: grid, width: 465"]
<colgroup><col><col span="6"></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Acct[/TD]
[TD]Com1[/TD]
[TD]Com2[/TD]
[TD]Com1 Change[/TD]
[TD]Com2 Change[/TD]
[TD]Acct Change[/TD]
[/TR]
[TR]
[TD="align: right"]01.01.2014[/TD]
[TD="align: right"]65000[/TD]
[TD="align: right"]1194[/TD]
[TD="align: right"]19,81[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]17.02.2014[/TD]
[TD="align: right"]73253[/TD]
[TD="align: right"]1330[/TD]
[TD="align: right"]21,94[/TD]
[TD="align: right"]11,4%[/TD]
[TD="align: right"]10,8%[/TD]
[TD="align: right"]12,7%[/TD]
[/TR]
[TR]
[TD="align: right"]19.02.2014[/TD]
[TD="align: right"]74148[/TD]
[TD="align: right"]1322[/TD]
[TD="align: right"]22,06[/TD]
[TD="align: right"]-0,6%[/TD]
[TD="align: right"]0,5%[/TD]
[TD="align: right"]1,2%[/TD]
[/TR]
[TR]
[TD="align: right"]23.02.2014[/TD]
[TD="align: right"]73492[/TD]
[TD="align: right"]1327[/TD]
[TD="align: right"]21,95[/TD]
[TD="align: right"]0,4%[/TD]
[TD="align: right"]-0,5%[/TD]
[TD="align: right"]-0,9%[/TD]
[/TR]
[TR]
[TD="align: right"]09.03.2014[/TD]
[TD="align: right"]70045[/TD]
[TD="align: right"]1339[/TD]
[TD="align: right"]20,89[/TD]
[TD="align: right"]0,9%[/TD]
[TD="align: right"]-4,8%[/TD]
[TD="align: right"]-4,7%[/TD]
[/TR]
[TR]
[TD="align: right"]13.03.2014[/TD]
[TD="align: right"]70752[/TD]
[TD="align: right"]1367[/TD]
[TD="align: right"]21,32[/TD]
[TD="align: right"]2,1%[/TD]
[TD="align: right"]2,1%[/TD]
[TD="align: right"]1,0%[/TD]
[/TR]
[TR]
[TD="align: right"]30.03.2014[/TD]
[TD="align: right"]62198[/TD]
[TD="align: right"]1294[/TD]
[TD="align: right"]19,82[/TD]
[TD="align: right"]-5,3%[/TD]
[TD="align: right"]-7,0%[/TD]
[TD="align: right"]-12,1%[/TD]
[/TR]
[TR]
[TD="align: right"]05.04.2014[/TD]
[TD="align: right"]62724[/TD]
[TD="align: right"]1302[/TD]
[TD="align: right"]19,96[/TD]
[TD="align: right"]0,6%[/TD]
[TD="align: right"]0,7%[/TD]
[TD="align: right"]0,8%[/TD]
[/TR]
[TR]
[TD="align: right"]12.04.2014[/TD]
[TD="align: right"]61738[/TD]
[TD="align: right"]1318[/TD]
[TD="align: right"]19,96[/TD]
[TD="align: right"]1,2%[/TD]
[TD="align: right"]0,0%[/TD]
[TD="align: right"]-1,6%[/TD]
[/TR]
[TR]
[TD="align: right"]08.04.2014[/TD]
[TD="align: right"]63023[/TD]
[TD="align: right"]1309[/TD]
[TD="align: right"]20,06[/TD]
[TD="align: right"]-0,7%[/TD]
[TD="align: right"]0,5%[/TD]
[TD="align: right"]2,1%[/TD]
[/TR]
[TR]
[TD="align: right"]11.06.2014[/TD]
[TD="align: right"]60825[/TD]
[TD="align: right"]1263[/TD]
[TD="align: right"]19,27[/TD]
[TD="align: right"]-3,5%[/TD]
[TD="align: right"]-3,9%[/TD]
[TD="align: right"]-3,5%[/TD]
[/TR]
[TR]
[TD="align: right"]27.06.2014[/TD]
[TD="align: right"]70594[/TD]
[TD="align: right"]1318[/TD]
[TD="align: right"]21,03[/TD]
[TD="align: right"]4,4%[/TD]
[TD="align: right"]9,1%[/TD]
[TD="align: right"]16,1%[/TD]
[/TR]
[TR]
[TD="align: right"]30.06.2014[/TD]
[TD="align: right"]71891[/TD]
[TD="align: right"]1328[/TD]
[TD="align: right"]21,06[/TD]
[TD="align: right"]0,8%[/TD]
[TD="align: right"]0,1%[/TD]
[TD="align: right"]1,8%[/TD]
[/TR]
[TR]
[TD="align: right"]07.07.2014[/TD]
[TD="align: right"]70519[/TD]
[TD="align: right"]1320[/TD]
[TD="align: right"]21,05[/TD]
[TD="align: right"]-0,6%[/TD]
[TD="align: right"]0,0%[/TD]
[TD="align: right"]-1,9%[/TD]
[/TR]
[TR]
[TD="align: right"]09.07.2014[/TD]
[TD="align: right"]73888[/TD]
[TD="align: right"]1326[/TD]
[TD="align: right"]21,1[/TD]
[TD="align: right"]0,5%[/TD]
[TD="align: right"]0,2%[/TD]
[TD="align: right"]4,8%[/TD]
[/TR]
[TR]
[TD="align: right"]10.07.2014[/TD]
[TD="align: right"]72968[/TD]
[TD="align: right"]1335[/TD]
[TD="align: right"]21,42[/TD]
[TD="align: right"]0,7%[/TD]
[TD="align: right"]1,5%[/TD]
[TD="align: right"]-1,2%[/TD]
[/TR]
[TR]
[TD="align: right"]12.07.2014[/TD]
[TD="align: right"]74865[/TD]
[TD="align: right"]1339[/TD]
[TD="align: right"]21,45[/TD]
[TD="align: right"]0,3%[/TD]
[TD="align: right"]0,1%[/TD]
[TD="align: right"]2,6%[/TD]
[/TR]
[TR]
[TD="align: right"]10.08.2014[/TD]
[TD="align: right"]73021[/TD]
[TD="align: right"]1309[/TD]
[TD="align: right"]19,91[/TD]
[TD="align: right"]-2,2%[/TD]
[TD="align: right"]-7,2%[/TD]
[TD="align: right"]-2,5%[/TD]
[/TR]
[TR]
[TD="align: right"]17.08.2014[/TD]
[TD="align: right"]71425[/TD]
[TD="align: right"]1304[/TD]
[TD="align: right"]19,55[/TD]
[TD="align: right"]-0,4%[/TD]
[TD="align: right"]-1,8%[/TD]
[TD="align: right"]-2,2%[/TD]
[/TR]
[TR]
[TD="align: right"]13.09.2014[/TD]
[TD="align: right"]64673[/TD]
[TD="align: right"]1129[/TD]
[TD="align: right"]18,61[/TD]
[TD="align: right"]-13,4%[/TD]
[TD="align: right"]-4,8%[/TD]
[TD="align: right"]-9,5%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Com1 to Acct corelation:[/TD]
[TD]Com2 to Acct corelation:[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]79,1%[/TD]
[TD="align: right"]90,4%[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

It shows you that Com2 price is more linked to Account price than Com1 price.
Then, based on other data (f.e., actual mix of metals in your mines), you can perform other analisys.
Hope it can helps for start

 

[Rx_]

Thank you very much. The 2 independent variables do show up fairly close to each other.
Actually the Mining Stock represents a 3rd variable as that it can vary even if the two metals stayed the same.
Silver has been more volatile. When the price of silver goes up 100%, the mine typically goes up 300% to 400%. So, the pure play silver mine account does support this strong linkage correlation

This should be a great start. thanks aga