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

#### Rx_

##### Board Regular
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

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

#### hohlick

##### New Member
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_

##### Board Regular
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

Replies
0
Views
405

1,196,028
Messages
6,012,956
Members
441,740
Latest member
abaz21

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back