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:

Date	Acct	Com1	Com2	Com1 Change	Com2 Change	Acct Change
01.01.2014	65000	1194	19,81
17.02.2014	73253	1330	21,94	11,4%	10,8%	12,7%
19.02.2014	74148	1322	22,06	-0,6%	0,5%	1,2%
23.02.2014	73492	1327	21,95	0,4%	-0,5%	-0,9%
09.03.2014	70045	1339	20,89	0,9%	-4,8%	-4,7%
13.03.2014	70752	1367	21,32	2,1%	2,1%	1,0%
30.03.2014	62198	1294	19,82	-5,3%	-7,0%	-12,1%
05.04.2014	62724	1302	19,96	0,6%	0,7%	0,8%
12.04.2014	61738	1318	19,96	1,2%	0,0%	-1,6%
08.04.2014	63023	1309	20,06	-0,7%	0,5%	2,1%
11.06.2014	60825	1263	19,27	-3,5%	-3,9%	-3,5%
27.06.2014	70594	1318	21,03	4,4%	9,1%	16,1%
30.06.2014	71891	1328	21,06	0,8%	0,1%	1,8%
07.07.2014	70519	1320	21,05	-0,6%	0,0%	-1,9%
09.07.2014	73888	1326	21,1	0,5%	0,2%	4,8%
10.07.2014	72968	1335	21,42	0,7%	1,5%	-1,2%
12.07.2014	74865	1339	21,45	0,3%	0,1%	2,6%
10.08.2014	73021	1309	19,91	-2,2%	-7,2%	-2,5%
17.08.2014	71425	1304	19,55	-0,4%	-1,8%	-2,2%
13.09.2014	64673	1129	18,61	-13,4%	-4,8%	-9,5%
				Com1 to Acct corelation:	Com2 to Acct corelation:
				79,1%	90,4%

<colgroup><col><col span="6"></colgroup><tbody>
</tbody>

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