Calculating the TREND(), FORECAST() looking at historical price of gold in Google Spreadsheet.
Being curious about how the forecast for Gold was actually reflected the real data in real life, I downloaded the historical prices of gold since 1970 until now ( 2013)
and used the Google Spreadsheet's TREND() and FORECAST() functions to see if they would come close to reality.
In fact, during the earlier years- the forecast() came close - however as the price of gold went up much much higher in 2012-2013 - the forecast() was way off.
Clearly, this forecast() has to be used with great precaution.
Again, I have not factored in the various affecting elements like Global slowdown, economic downturn the world has faced - so the historical prices do not reflect how the performance of gold in future can hold.
So - here's an example:
Strangely, the gold prices did not reflect the trending pattern from 1969 to 2013.
This spreadsheet I downloaded can be found in this URL http://www.gold.org/investment/statistics/gold_price_chart/ has data from 1969 in USD.
When I experimented this using the TREND() and the FORECAST () functions - it did not turn out the way I expected showing a linear pattern.
I used my google account, Google Drive -> Spreadsheet and only looked at the Year and the price in USD.
First, Let's look at the starting and ending dates in the actual spreadsheet.
Let's look at the starting date.. and the price of gold - i.e 1970 and price of gold was 36$ for an ounce.
Now, lets look at the ending date - here the price of gold was up at 1600.00 in spring 2013.
There are 500 lines in this spreadsheet , or 500 months worth of data.
Having Imported the spreadsheet into the Google Spreadsheet, Now, click on the Chart Icon.
let's choose the "Charts" -> and Trend
and click on Insert the "Trend" ->this shows the type of chart - indicating the price of gold in 1970 from 34$ and ounce.
and now in Spring 2013 - at 1600 USD approx.
This is the real picture of how gold has fared over the course of time.
Now, let's experiment with the TREND ( ) and FORECAST () for every 100 months and see if the data from the chart, reflects our formula calculated.
For this discussion - the TREND() and FORECAST() yield similar results , so I have used the TREND() function .
First for 100 days - the actual in the original spreadsheet is = 181.00
and by using the TREND() = we get = 183.00 - so this is good. indicating - that if we had used this function for - then this would have given close to accurate results.
let's repeat the same for the next 300 months.
Actual price of gold was 383.00
TREND () is 484.00 ( so trend() overcalculated the data by 100 USD higher )
Had you taken a business decision on this - this would mean some serious consequences. ( profit or loss )
Now, we repeat the same for 500 months, as this is critical - since the price of gold shot up higher than expected in 2012 .
The actual price of gold is in line 521 at 1469.00 $ an ounce, and the calculated price using the TREND () in the next line.
Notice the actual price of gold was 1469.00 but the calculated price using the TREND() was 800.00 USD an ounce - which is much much lower ( almost twice as low )
So, clearly the TREND() cannot be used by looking at the historical prices alone.
We might have to use a different mechanism to predict the price of Gold.
( Here my experiment was just to show that historical prices is not a reflection of future data )
The last line 5/31/2013 is what I entered to come up with E522.
This spreadsheet I downloaded can be found in this URL http://www.gold.org/investment/statistics/gold_price_chart/ has data from 1969 in USD.
When I experimented this using the TREND() and the FORECAST () functions - it did not turn out the way I expected showing a linear pattern.
I used my google account, Google Drive -> Spreadsheet and only looked at the Year and the price in USD.
First, Let's look at the starting and ending dates in the actual spreadsheet.
Let's look at the starting date.. and the price of gold - i.e 1970 and price of gold was 36$ for an ounce.
Now, lets look at the ending date - here the price of gold was up at 1600.00 in spring 2013.
There are 500 lines in this spreadsheet , or 500 months worth of data.
Having Imported the spreadsheet into the Google Spreadsheet, Now, click on the Chart Icon.
let's choose the "Charts" -> and Trend
and click on Insert the "Trend" ->this shows the type of chart - indicating the price of gold in 1970 from 34$ and ounce.
and now in Spring 2013 - at 1600 USD approx.
This is the real picture of how gold has fared over the course of time.
Now, let's experiment with the TREND ( ) and FORECAST () for every 100 months and see if the data from the chart, reflects our formula calculated.
For this discussion - the TREND() and FORECAST() yield similar results , so I have used the TREND() function .
First for 100 days - the actual in the original spreadsheet is = 181.00
and by using the TREND() = we get = 183.00 - so this is good. indicating - that if we had used this function for - then this would have given close to accurate results.
let's repeat the same for the next 300 months.
Actual price of gold was 383.00
TREND () is 484.00 ( so trend() overcalculated the data by 100 USD higher )
Had you taken a business decision on this - this would mean some serious consequences. ( profit or loss )
Now, we repeat the same for 500 months, as this is critical - since the price of gold shot up higher than expected in 2012 .
The actual price of gold is in line 521 at 1469.00 $ an ounce, and the calculated price using the TREND () in the next line.
Notice the actual price of gold was 1469.00 but the calculated price using the TREND() was 800.00 USD an ounce - which is much much lower ( almost twice as low )
So, clearly the TREND() cannot be used by looking at the historical prices alone.
We might have to use a different mechanism to predict the price of Gold.
( Here my experiment was just to show that historical prices is not a reflection of future data )
The last line 5/31/2013 is what I entered to come up with E522.
Conclusion : Historical prices of Gold is not a reflection of future prices.
Gold prices are not incremental or show linear upward or downward trend.
Gold prices are seasonal in some regions of the world.
Gold price depends on global economy, and other factors like monetary conditions and slowdown or acceleration of productivity, availability, buy and sell demands.
My above experiment was to use the Google Spreadsheet's TREND() and FORECAST () functions to see if historical prices were a reflection of how it'd look like, which seemed true until 2008 and then the prediction was not even close.
