ES Emini futures database - very few primary fields date, Day and Time dependent

[araise]

My first attempt here, folks, bear with me. a few years a go I could normalize a Foxpro DB to 6 levels, now I can barely spell database --- you don't use it , you lose it. Glad I found this forum!

Database name: ES Emini
Table I. Primary table consists of 6 fields: Date, Day of Week, Open, High, Low and Close (OHLC are numeric)
Table II. Appoximately 50 fields, all calculated from the fields in Table I, and obviously Date and DOW dependent

The data is currently on an excel spreadsheet. very difficult to program ad hoc queries in a spreadsheet.



The one thing I do remember is that I could, using a database, run circles around spreadsheets in many respects.

Anyway, I'd appreciated assistance/ suggestions in exactly how to relate the two (or if you have a better suggestion on design) . DATE seems to be the key field for the ID field, but Access 2013 seems to have a problem with me using it.
Many thanks in advance!!

[CJ_London]

date is a reserved word which is probably why - see this link

https://support.office.com/en-us/art...ad=US&fromAR=1

other comments - you don't need day of week field, it is calculated in a query (use the weekday function)

From your brief description, you don't need table 2 either if all fields are calculated from table 1 - however unless you are only tracking one stock, I would have thought you would also have a field to identify the stock

[Micron]

If your primary table fields are really called those names, you have field/object naming issues to start with. If table II fields would need to be added/deleted as business conditions change (e.g. you add another department or entity and you need another field) you have normalization issues as well. If your fields hold calculated data, that is a potential big issue as well.

So this is my opinion: Access is for data 'storage', retrieval and doing what you might call relational grouping or presentation. It does some 'low level' aggregate functions but it's not nearly as powerful as Excel for that, nor would I use Access for charting. Given all that, you might want to do a bit of research on database normalization and the use of reserved words for design purposes and try to avoid more issues like the one related to calling a field "Date". There is so much info on the first that I say Google it and find some that make sense to you and maybe even reflect your project. As for names: http://allenbrowne.com/AppIssueBadWord.html

Given all that, I think you'd have to provide a lot more info if you're looking for normalization advice. One suggestion would be to post here a copy of the Excel file so we can see what you've got, and a comprehensive explanation of what you are trying to achieve. One thing that often doesn't work well for this sort of situation is if you provide unrelated example info that has little bearing on the real situation. Suggestions are given in response to text examples, only to find out that the real situation involves numbers.

[ssanfu]

You might look at this thread... Post #2
https://www.accessforums.net/showthread.php?t=59435

[araise]

Well, thx for the feedback.
Yes, those are the real field names and I am only referencing one instrument - not a stock , but an index future, the S&P Emini.
It appears I do not need a second table in that all calculation should just be done on the primary table, so obviously no normalization is necessary.

My primary purpose in thinking about using Access rather than Excel was because on the fly queries were one helluvalot easier in a database table as opposed to Excel. The results of the queries would then be fed back into an Excel spreadsheet in order to do multiple statistical analysis -- combinations, permutations, linear regression analysis and the like.

It sounds like I'd be better off just sticking with my current spreadsheet.

I do appreciate the help and the feedback. Thanks again everyone for taking the time to respond !!




I've spent a couple of hrs on Dr Soper's site. Good info.

[CJ_London]

depends on how many days of data you are storing, but excel starts to get slow with too many records

you could keep your data in access and load filtered/calculated data through a sql connection - you'll have the benefit of indexing in access to keep performance high and a reduced excel file size

[ssanfu]

...I do not need a second table in that all calculation should just be done on the primary table...

No, tables just store data.
Calculations would be done in one (or more) queries. Depending on the calculation, you could use the built in functions or write some UDFs specific to your requirements.

[araise]

good point on using queries for calculations - I keep just over 4 yrs of daily data currently in excel with approximately 200 different calculated data points per day. In other words, I currently calculate these 200 data points from the four (Open, High Low and Close ) primary fields.
i can see the advantage of speed in indexing for sure
thx