Import Data
Return Model
Comma-Separated Value File (.csv)
Import Data
Event Study Metrics
Copyright © 2011 Event Study Metrics UG (haftungsbeschränkt)
This software product, including program code and manual, is copyrighted, and all rights
are reserved by Event Study Metrics UG (haftungsbeschränkt). Your rights to the software
are governed by the accompanying software license agreement. Under the copyright law,
this manual may not be copied, in whole or in part, without the prior written permission of
Event Study Metrics UG (haftungsbeschränkt).
Disclaimer
Event Study Metrics UG (haftungsbeschränkt) assumes no responsibility for any errors that
may appear in this manual or the Event Study Metrics software. The user assumes all
responsibilities for the selection of the program to achieve intended result, and for the
installation, use, and results obtained from the Event Study Metrics software.
Trademarks
Event Study Metrics is a registered trademark of Event Study Metrics UG
(haftungsbeschränkt). Windows and Excel are registered trademarks of Microsoft
Corporation. Other company and product names mentioned herein are trademarks of their
respective companies. Mention of third-party products is for informational purposes only
and constitutes neither an endorsement nor a recommendation. Event Study Metrics UG
(haftungsbeschränkt) assumes no responsibility with regard to the performance or use of
these products.
Event Study Metrics UG (haftungsbeschränkt)
Schornsberg 21
D-53332 Bornheim
[email protected]
www.eventstudymetrics.com
May 30, 2014
Import Data
Return Model
The Return Model contains the market data, which allows you to
implement the market model, market adjusted return, the CAPM, or a
multifactor model in your study. You are able to import your Return Model
from a comma-separated value file (.csv). Most spreadsheet or
database programs allow you to store your data as a comma-separated
value file (.csv). If you have your data in a different spreadsheet format
(for example .xlsx), please follow the instructions in the Creating a
comma-separated value file (.csv) section.
Structure
The first line of the spreadsheet contains the column headings. Event
Study Metrics allows you to create up to six columns, depending on the
model you want to apply:
Mandatory Columns:
1. Time series: The first column must contain the times series of
your market index.
The date format should correspond to the format selected on the
Settings form within the Event Study Metrics software. A valid
date is either of the form MM/DD/YYYY (format 1) or
1
Import Data
DD.MM.YYYY (format 2). If you have a different date format in
your Return Model, please conduct the following steps:
a) Select the cells in the Event Date column:
b) Right click on the selected cells and click the Format Cells tab:
2
Import Data
c) In the Format Cells box, click the Number tab and select Date
in the Category list. Under Type you are able to select the
required format (MM/DD/YYYY or DD.MM.YYYY):
Note: The displayed date formats in the Type box are based on
the selected language under Locale (location). You may
select e.g. English (USA) in order to find the right format.
If you can’t find the required format under the Type list, select a
format that is close to the required type and click Custom
under the Category list:
3
Import Data
In the Type box you are able to adjust the date format by
entering the required pattern (MM/DD/YYYY or DD.MM.YYYY):
d) Confirm any changes by clicking OK.
2. Market Index: The second column must contain the index
values or the return data. You are able to specify the type of your
data (e.g. price, return) the Raw Data section within the ESM
software.
4
Import Data
Optional Columns:
3. Interest Rate: If your model is defined by excess returns, the
third column must contain the corresponding interest rate (e.g.
risk-free interest rate). You should use an interest rate according to
your individual data frequency.
Factor Models: Event Study Metrics allows you to implement up
to three additional factors in the columns 4 to 6. If you plan to
apply a factor model, all data must be defined in terms of returns
instead of prices.
4. SMB: The shortcut stands for ‘small minus big’. The factor should
capture the excess return of small over big stocks (measured
by market cap).
5. HML: The shortcut stands for ‘high minus low’. The factor should
capture the excess return of stock with a high market-to-book
ratio over stocks with a low market-to-book ratio.
6. UMD (MOM): The shortcut stands for ‘up minus down’. The factor
should capture the excess return of past winning over past
losing stocks (momentum factor).
5
Import Data
Creating a comma-separated value file (.csv)
The required format of your comma-separated value file (.csv) depends on
the Format and the Delimiter selected on the
Settings form within the
Event Study Metrics software:
Note: The basic format of the comma-separated value file (.csv) depends
on the Region and Language Settings on your computer. Because
of the variety of different settings, this manual provides a uniform
solution.
1. Click the Start
button at the bottom left corner of the desktop
(If you are using Windows 8 click on the Settings button).
6
Import Data
2. Click the Control Panel button and select Clock, Language and
Region section:
3. Select the Region and Language section:
7
Import Data
4. Select the Formats tab and then click on the Additional Settings
button:
8
Import Data
5. Under the Numbers tab you are able to adjust the Decimal
symbol, the Digit grouping symbol (Thousands separator) and
the List separator (Delimiter) as follows:
a) If you prefer the Format 1 and a comma (,) as the Delimiter
within the ESM software, please fill in the box:
-
Decimal symbol: dot (.)
Digit grouping symbol: comma (,)
List separator: comma (,)
9
Import Data
b) If you prefer the Format 2 and a semicolon (;) as the
Delimiter within the ESM software, please fill in the box:
-
Decimal symbol: comma (,)
Digit grouping symbol: dot (.)
List separator: semicolon (;)
6. Confirm by clicking OK.
7. Open your Return Model file in Excel:
10
Import Data
1) If the Return Model is already a comma-separated value file
(.csv), click the icon
changes.
to save the file and to confirm any
2) If you have your data in a different spreadsheet format, you
are able to save the data as a comma-separated value file
(.csv) by conducting the following steps:
a) Click Save as in the File list:
11
Import Data
b) Select CSV (Comma delimited) from the File Type dropdown
list and click Save:
c) Excel will display two notifications. Both should be approved.
Finally, the Return Model is saved as a separate commaseparated value file (.csv).
12