Simple linear regression: difference of results in Stata when compared to Excel (why?)

[Michael Craig]

I performed a simple linear regression in Stata and in Excel by relating the number of hotel arrivals for entire Switzerland booked by German travelers (Logiernächte or coded as CH_AN_SUM) to the exchange rate of EUR and CHF (coded FX) for 165 months.


Stata:

regress CH_AN_SUM FX

Source | SS df MS Number of obs = 164
-------------+---------------------------------- F(1, 162) = 18.43
Model | 3.1587e+11 1 3.1587e+11 Prob > F = 0.0000
Residual | 2.7761e+12 162 1.7136e+10 R-squared = 0.1022
-------------+---------------------------------- Adj R-squared = 0.0966
Total | 3.0919e+12 163 1.8969e+10 Root MSE = 1.3e+05

------------------------------------------------------------------------------
CH_AN_SUM | Coef. Std. Err. t P>|t| [95% Conf. Interval]
-------------+----------------------------------------------------------------
FX | -217368.3 50629.01 -4.29 0.000 -317346.2 -117390.4
_cons | 933585 67871.59 13.76 0.000 799557.9 1067612

------------------------------------------------------------------------------


Excel:





Why is there such large difference in the coefficients and in R2?

Guess something went wrong, I copied the data from Stata to check any differences in Excel but found nothing. Excel ANOVA also says that there are 165 observations (which is true) but Stata says 164 (However, should not be material)


What I actually need to calculate from that is the price elasticity. It has yet been found somewhere between 0.x to 2.x or something (if FX rate goes up - more expensive - less demand).

In the literature the relationship is usually made in a log log regression model but that did absolutely not work (no results). Maybe this is useful when I want to consider potential differences of regions (cities vs. tourist regions)?

Any help highly appreciated.

[Michael Craig]

the file from excel

[Michael Craig]

Excel (graph seems not work)

SUMMARY OUTPUT
Regression Statistics
Multiple R	0,302268754
R Square	0,091366399
Adjusted R Square	0,08579196
Standard Error	132284,9874
Observations	165
ANOVA
	df	SS	MS	F	Significance F
Regression	1	2,86818E+11	2,86818E+11	16,39024036	7,95009E-05
Residual	163	2,85239E+12	17499317887
Total	164	3,13921E+12
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95,0%	Upper 95,0%
Intercept	916773,713	68112,75238	13,45964861	3,01501E-28	782276,5951	1051270,831	782276,5951	1051270,83
X Variable 1	-205943,1703	50869,17949	-4,048486182	7,95009E-05	-306390,7045	-105495,636	-306390,7045	-105495,64

[Michael Craig]

Check the data again. There was one FX value missing that has certainly caused the problem. Remaining difference probably due to rounding

. regress CH_AN_SUM FX

Source | SS df MS Number of obs = 165
-------------+---------------------------------- F(1, 163) = 16.39
Model | 2.8682e+11 1 2.8682e+11 Prob > F = 0.0001
Residual | 2.8524e+12 163 1.7499e+10 R-squared = 0.0914
-------------+---------------------------------- Adj R-squared = 0.0858
Total | 3.1392e+12 164 1.9142e+10 Root MSE = 1.3e+05

------------------------------------------------------------------------------
CH_AN_SUM | Coef. Std. Err. t P>|t| [95% Conf. Interval]
-------------+----------------------------------------------------------------
FX | -205945.6 50869.23 -4.05 0.000 -306393.3 -105498
_cons | 916777 68112.84 13.46 0.000 782279.7 1051274
------------------------------------------------------------------------------

[Clyde Schechter]

Thank you for following up with your resolution, and for closing the thread.

[Michael Craig]

Hi Clyde, I am new to the forum. Is there a (technical) way to close the thread? Something to klick in order top signal that the answer is solved or similar?

[Clyde Schechter]

No, there is no technical close to the thread. You just post something stating that your problem is solved, usually with thanks to those who helped. That's all. It's what you did in #4!