# Strategy for analysing stock history data from multiple files with SPSS

I have a folder with a hundred comma separated files (CSVs) of which the filename equals a company stock exchange followed by it's symbol, delimited with a "_", eg: NASDAQ_MSFT.csv

Each file contains historical daily stock information, eg. a csv file looks like:

Date,Open,High,Low,Close,Volume
29-Dec-00,21.97,22.91,21.31,21.69,93999000
28-Dec-00,22.56,23.12,21.94,22.28,75565800
27-Dec-00,23.06,23.41,22.50,23.22,66881000
...
5-Jan-00,55.56,58.19,54.69,56.91,62712600
4-Jan-00,56.78,58.56,55.94,56.31,52866600
3-Jan-00,58.69,59.31,56.00,58.34,51680600


Now, I want to analyse this information (analyse the companies as if the company name is a column field rather than a new list of fields). But there are a couple of issues:

1. Each company has it's own file with the symbol and exchange as filename
2. Some companies start at different dates than others. Eg. some might have a range of 30 days while others have a range of 30 months (every step is still 1 day difference though).

I am using SPSS as my analysis tool. My question is, how can I import these files to perform senseful analystical operations on them?

For example, I wish to see the average slope of the open price of all companies together, etc.

Getting the data into SPSS

Typically when you organize your data, the rows in the data matrix correspond to the units of analysis, and the columns correspond to attributes of those units. Given the description of your data, you have basically three possible units of analysis, companies, days, and companydays. Given the format of your CSV files it will be easiest to either import the data as "days" or "companydays". I'm going to suggest you import it as "company*days" as it is easier to transform the data to the other units of analysis later on if needed in that format.

So to import the data, you can use this syntax (just replace with the right variable names and formats). I just generated this example through the GUI.

GET DATA
/TYPE=TXT
/FILE='H:\NASDAQ_MSFT.csv'
/DELCASE=LINE
/DELIMITERS=","
/ARRANGEMENT=DELIMITED
/FIRSTCASE=2
/IMPORTCASE=ALL
/VARIABLES=
time F3.0
panel F1.0
price F16.15.
EXECUTE.
Dataset Name NASDAQ_MSFT.


I would then make a variable that identifies what company the dataset correponds to.

string company (A100).
compute company = "NASDAQ_MSFT".
execute.


After you have loaded the csv files into SPSS datasets, you can then concatenate them together with the add files command. Note this adds new rows to the dataset, not columns, hence the need to make a variable that identifies each company.

ADD FILES file = 'NASDAQ_MSFT'
/FILE = 'OTHER_DATASET'
/FILE = 'OTHER_DATASET'.


This will obviously be tedious, but if it only needs to be done once I would just write a macro to semi automate the job. If this needs to be done periodically I would suggest posting the question to one of the other SPSS forums (links provided in the SPSS tag wiki), I wouldn't be surprised in someone has already made a tool via Python to accomplish this (i.e. getting all the files in one directory and doing whatever you want with them).

Analyzing the data

To be able to give useful advice you need to be able to formulate more specific questions. Different hypotheses would require different answers to your second question. Below I have posted a simulated example getting the average slope against time for all companies (via the split file command) and two plotting examples I cooked up with the GUI. If they are not clear enough just say so in a comment and I will add further explanation. For documentation on SPSS's graph language and other syntax see this page.

*SIMULATING DATA.
input program.
loop #company = 1 to 10.
loop time = 1 to 100.
compute company = #company.
compute price = RV.NORMAL(#company,.10).
variable level company (NOMINAL).
end case.
end loop.
end loop.
end file.
end input program.
execute.

*USING split file to get the average slope against time for every company.
sort cases by company.
split file by company.
REGRESSION
/MISSING LISTWISE
/STATISTICS COEFF OUTS R ANOVA
/CRITERIA=PIN(.05) POUT(.10)
/NOORIGIN
/DEPENDENT price
/METHOD=ENTER time.
split file off.

*Multiple lines on one chart via Chart Builder GUI, note the panel variable needs to be coded as nominal.
GGRAPH
/GRAPHDATASET NAME="graphdataset" VARIABLES=time price company MISSING=LISTWISE REPORTMISSING=NO
/GRAPHSPEC SOURCE=INLINE.
BEGIN GPL
SOURCE: s=userSource(id("graphdataset"))
DATA: time=col(source(s), name("time"))
DATA: price=col(source(s), name("price"))
DATA: panel=col(source(s), name("company"), unit.category())
GUIDE: axis(dim(1), label("time"))
GUIDE: axis(dim(2), label("price"))
GUIDE: legend(aesthetic(aesthetic.color.interior), label("company"))
ELEMENT: line(position(time*price), color.interior(panel), missing.wings())
END GPL.

*Small multiples via the IGRAPH GUI.
IGRAPH
/VIEWNAME='Line Chart'
/X1=VAR(time) TYPE=SCALE
/Y=VAR(price) TYPE=SCALE
/PANEL=VAR(company)
/COORDINATE=VERTICAL
/YLENGTH=5.2
/X1LENGTH=6.5
/CHARTLOOK='NONE'
/CATORDER VAR(company) (ASCENDING VALUES OMITEMPTY)
/LINE(MEAN) KEY=ON STYLE=LINE DROPLINE=OFF INTERPOLATE=STRAIGHT BREAK=MISSING.

• This is very helpful, thanks. Your answer will help me get further. I understand the need for more specific questions, which I shall do in the future. Now that I have a starting point, I can get into more specific areas. Thanks again. – Tom Feb 4 '11 at 17:07