data calculations

[_______Tim_______]

Hi,

I am very new to Access. Please excuse any dumb
questions. I have put a data file into Access that looks
exactly like the following sample...

IACI,Interactivecorp,,40.23,40.42,39.09,39.37,5554459
MNST,Monster Worldwide Inc,,24.75,24.8,23.25,23.45,3776287
RCRC,Rc2 Corp,,15.25,15.91,15.2,15.55,88313
FL,Foot Locker Inc,,14.51,14.51,14.35,14.36,591600
CBB,Cincinnati Bell Inc New,,5.9,5.97,5.69,5.8,1013900
RGS,Regis Corp Minn,,30.7,31.22,30.6,31.19,136700
ESIOE,Electro Scientific Inds,,17.1,17.3,17.0,17.01,135735
DKHR,D & K Healthcare Res
Inc,,17.575,18.0,17.575,17.68,42884
PHS,Pacificare Health Sys
Del,,57.92,59.26,57.92,58.81,622400

There are several thousand lines of daily stock data.
Column/Field headings are Symbol, Company, Field 3, Open,
High, Low, Close, Volume. How can I calculate a permanent
new column/field in the database that is [Open]-[Close]
and another permanent new column/field that asks for the
Dow Open and Close and the NASDAQ Open and Close to
calculate a beta, ([Dow Security Close, 3 place symbol]-
[Dow Security Open, 3 place symbol])/([Dow Close]-[Dow
Open]) to one decimal place. All other symbols are NASDAQ
using the same formula. Also it would be nice if at that
point the Dow Open and Close and the NASDAQ Open and Close
were added to the database. The data is always in the
same format.

I am using Office 2000 and Windows ME. Any help is
greatly appreciated.

Regards, Tim

 

[Steve Schapel]

Tim,

It appears that your data is presently in a comma-delimited text
format, otherwise known as csv, so I presume you have imported it into
Access from a csv file of some sort. If you want to use it in Access,
you will need to first of all separate the text file into the
individual fields. It is not clear from your post whether you know
how to do this, or whether you have already done this.

Then, to derive the calculated values you are looking for, you would
use a query. For example, you might type this into the Field row of
the query design grid...
Movement: [Close]-[Open]
To create a permanent field in the table for this data, i.e. save and
store this value, would generally be regarded as invalid database
practice.

In fact, it is not really clear to me why you would use Access for
this project (although there well may be good reasons, based on
information I don't know about). I may be out of line here, but on
the face of it, it seems to be much more of a spreadsheet task than a
data management task, and Excel may be more suitable to your purpose.

- Steve Schapel, Microsoft Access MVP

 

[_______Tim_______]

Steve,

Thank you for taking the time to look at this. If it were
just the stock data I would agree with you. But I also
use the option data, which is at least 130,000 rows of
data. I think that is beyond the capacity of Excel. I
could manipulate the stock data in Excel, no problem. But
I think I need to learn how to do calculations and
manipulation in Access for the Option data. I would
rather do my manipulations in Excel but I have not yet
been successful in importing the csv file to Excel, it's
too large. I think with the query feature in Access I can
do some of the first manipulations in Access so the field
of candidates is greatly reduced and can be easily
manipulated in Excel. Please advise.

Regards, Tim

-----Original Message-----
Tim,

It appears that your data is presently in a comma- delimited text
format, otherwise known as csv, so I presume you have imported it into
Access from a csv file of some sort. If you want to use it in Access,
you will need to first of all separate the text file into the
individual fields. It is not clear from your post whether you know
how to do this, or whether you have already done this.

Then, to derive the calculated values you are looking for, you would
use a query. For example, you might type this into the Field row of
the query design grid...
Movement: [Close]-[Open]
To create a permanent field in the table for this data, i.e. save and
store this value, would generally be regarded as invalid database
practice.

In fact, it is not really clear to me why you would use Access for
this project (although there well may be good reasons, based on
information I don't know about). I may be out of line here, but on
the face of it, it seems to be much more of a spreadsheet task than a
data management task, and Excel may be more suitable to your purpose.

- Steve Schapel, Microsoft Access MVP

Hi,

I am very new to Access. Please excuse any dumb
questions. I have put a data file into Access that looks
exactly like the following sample...

IACI,Interactivecorp,,40.23,40.42,39.09,39.37,5554459
MNST,Monster Worldwide Inc,,24.75,24.8,23.25,23.45,3776287
RCRC,Rc2 Corp,,15.25,15.91,15.2,15.55,88313
FL,Foot Locker Inc,,14.51,14.51,14.35,14.36,591600
CBB,Cincinnati Bell Inc New,,5.9,5.97,5.69,5.8,1013900
RGS,Regis Corp Minn,,30.7,31.22,30.6,31.19,136700
ESIOE,Electro Scientific Inds,,17.1,17.3,17.0,17.01,135735
DKHR,D & K Healthcare Res
Inc,,17.575,18.0,17.575,17.68,42884
PHS,Pacificare Health Sys
Del,,57.92,59.26,57.92,58.81,622400

There are several thousand lines of daily stock data.
Column/Field headings are Symbol, Company, Field 3, Open,
High, Low, Close, Volume. How can I calculate a permanent
new column/field in the database that is [Open]-[Close]
and another permanent new column/field that asks for the
Dow Open and Close and the NASDAQ Open and Close to
calculate a beta, ([Dow Security Close, 3 place symbol]-
[Dow Security Open, 3 place symbol])/([Dow Close]-[Dow
Open]) to one decimal place. All other symbols are NASDAQ
using the same formula. Also it would be nice if at that
point the Dow Open and Close and the NASDAQ Open and Close
were added to the database. The data is always in the
same format.

I am using Office 2000 and Windows ME. Any help is
greatly appreciated.

Regards, Tim

.