Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Database Overview Question

  Asked By: Harry    Date: Jan 31    Category: MS Office    Views: 1070
  

I recently retired and began work on a database project that I expect
to continue working on for the rest of my life.

The objective of this project is to accumulate data on sporting
events; then use that database to test various "betting systems" that
I come across to see if they are valid for picking winners. I need
flexibility in changing various selection criterea ("tweaking").

With this objective in mind I procured a copy of MS Office-XP, "Excel
2003 for Dummies", "Excel VBA Programming for Dummies", and
"Statistical Analysis with Excel for Dummies".

Now, after several week's of full-time work, I'm wondering if Excel
VBA is really the right tool for the job. It doesn't seem
particularly user-friendly when it comes to database manipulation--or
it could be that I'm just not skilled in its use (the latter is
certainly true).

An example of what I'm talking about: After setting up my initial
baseball database of 5 years' games (one year's games = 26 columns x
6000 rows) I find that I want to insert columns of additional info
where they are most useful. Unfortunately this means I have to go
back and edit all of the macros I have written ( MANY!!). And as soon
as I get them edited, my "work-in-progress" may need some other
modification which will invalidate the macros once again.

Note: I also have equivalent databases for Pro Football, College
Football, Pro Hockey, Pro Basketball, College Basketball--all sports
which are actively wagered upon. I have full records for the past 5
years; and every day I update with all the info of all the games in
all the sports which were played the previous day.

Also I would like to automate a lot of the data entry which comes
straight off of websites--WebQuery doesn't seem to handle this very
well--I can't do successful downloads automatically although I can do
them manually, which doesn't make sense to me. If my computer can do
the job with me pushing the buttons(copy/paste), why can't it do the
same job with a macro pushing the buttons????

I've heard about Access, SQL, Perl, etc., but don't really know
anything about them.

Based on what I've said above, would you experts suggest that I stick
with Excel and VBA; or should I switch to something else which is
better designed for my purposes??

Share: 

 

18 Answers Found

 
Answer #1    Answered By: Nahal Malik     Answered On: Jan 31

I would probably not use Excel for data  of this quantity, however, others would.

I wouldn't say that databases are much more flexible at things like column
insertion - provided you've set your Excel code up properly in the first place.
You should be using named ranges or column headings or lookups to locate your
data columns, rather than simple column letters.

Excel is good at statistics and graphs, so that's an advantage.

Excel isn't good at lots of different views of the same data - databases are
better.

However, it's up to you. Both technologies can be made to do the work.

The problem is that you've jumped right in the deep-end, with a technology that
you are inexperienced with. You will need to have the patience to rework as you
learn.

 
Answer #2    Answered By: Billy Evans     Answered On: Jan 31

I have not set my code up properly in the first place--I've been using
relative references off of an activecell in a "key" column--just doing
it intuitively. And what a tangled web I have woven! I will try to
find some stuff to read on named ranges and column headings. Also
Pascal showed me some code which makes navigation a bit easier.

Since you would use something other than excel, what would you
use?--In other words, what should I consider to be the next step up,
if I want to do a little research? I'm mainly concerned about putting
a bunch of work  into a vehicle which will turn out to be unable to
handle the load down the line.

And what about those WebQueries that can't handle what I can do
manually--does that make sense to you???

 
Answer #3    Answered By: Isam Bashara     Answered On: Jan 31

I would use Access because I know it. Also because it is easy to create
queries and to produce different summary reports.

Access would have no problems with additional columns  being inserted because
each column would have a unique name and would be referred to by name.

The issue with webqueries is that web pages are not structured consistently.
When you look at a webpage you can say "That stuff in the centre at the bottom
is what I want, copy  it and paste it. However to the computer it is just part
of a stream of bytes.

The webquery tools work  well _if_ the data  is in a separate table on the
webpage and there are not too many tables, nested tables, etc. This depends
on the whim of the web designer and the tools he is using. A web page
designed in MS Word looks (to the computer) quite different from one handmade
in html or one prepared using DreamWeaver. These differences are not visible
to you and therefore it is easy to pick stuff off the surface of the page
without worrying about the structure underneath.

It makes no difference what application you use to extract the data. This
problem will always exist as it is at a different level from the application
itself. Information which is presented in an obscure fashion behind the
scenes will not flow into access any better than it will into excel. Actually
it could be more difficult because with excel  you can import and then tidy,
rearrange columns, etc. To import data into Access you have to import into
the correct columns in the first place and the data has to be of the right
type. A string (such as "no play") in a numeric field will cause access to
spit the whole row out.

 
Answer #4    Answered By: Volker Fischer     Answered On: Jan 31

However your WebQuery answer, while entirely accurate, does not
address the specific situation that I have (mainly because I didn't
explain it in detail). I just download the whole page, with no table
selection necessary, since WebQuery has the problems you described.

So here it is--a very simple situation: These are the steps that I do
physically and that I would like to accomplish with a macro:

1. Go to
www.donbest.com/.../all-games.shtml?20070408
(note that this page can be easily activated by hyperlink)
(note that the last 8 digits are the date, which can be easily
incremented by macro)

2. Click Edit
3. Click Select All
4. Click Copy

5. Key Control z on the keyboard. This activates a macro that I have
already created. This macro simply goes to the active cell on my
excel sheet, does a "paste special" with the HTML selection  (default),
then selects "match destination formatting", then chops off all the
extra garbage that I don't want, leaving only the table of data  that I
do want in the proper place on the worksheet.

So all I'm asking a new macro to do is steps 2,3, and 4. It blows my
mind that people are telling me it can't be done.

 
Answer #5    Answered By: Jack Williams     Answered On: Jan 31

Use Sendkeys to send Ctrl-A Ctrl-C to Internet explorer (after you activate
the hyperlink)


Something like

Activate the hyperlink
SendKeys "^(AC)", true

Application.activate ' to bring you back to excel

Name of macro called by ctrl-z

I think this will need some experimenting and tidying but it should be enough
to get you started.

 
Answer #6    Answered By: Victoria Hughes     Answered On: Jan 31

SendKeys--something else to look into--
Is there an easy-to-understand book that has all this stuff in one
place?--QueryTables, SendKeys, and I'm sure there are many more--"VBA
for Dummies" just doesn't pack this kind of horsepower--Any
recommendations?-

 
Answer #7    Answered By: Ramond Fischer     Answered On: Jan 31

in regards to the subject, I was once read (I'm a newbie tho :)) that we
could use an SQL statement in Excel Macro. Can someone point me to an
example ?.

 
Answer #8    Answered By: Cedric Sanders     Answered On: Jan 31

SQL can be used to import external data  from a database. If you open import
external data and do not use the wizard there is a button in MS query to do
that.

You can also use SQL strings programatically. It requires an understanding of
???DAO or ???ADO (a bit outside my area of expertise, tho I have used it in
Access.).

 
Answer #9    Answered By: Edjo Chalthoum     Answered On: Jan 31

What are you looking to do - export external data, or just use SQL on a
worksheet?

 
Answer #10    Answered By: Beau Smith     Answered On: Jan 31

Actually, i was just thinking if there is a way to use SQL on a worksheet.

 
Answer #11    Answered By: Birk Fischer     Answered On: Jan 31

You can record a web query to download a whole page. Then you need to
find something on the page which IS consistent for each date, such as
the table heading eg. NBA BASKETBALL, then look for one of its
subheadings, which you'll know is going to be somewhere below and to
the right within a limited range, then you'll be able to grab only the
data you want and place it elsewhere.

I tend to steer away from keeping html formatting. I recorded the
following which leaves you with a sheet having something you can work
with:
Sub Macro4()
'
' Macro4 Macro

'

'
With ActiveSheet.QueryTables.Add(Connection:= _

"URL;www.donbest.com/.../all-games.shtml?20070408",
_
Destination:=Range("A1"))
.Name = "all-games.shtml?20070408"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = False
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

Delete the data  when you've finished farming it and start again with
the next date.

I did this some time ago to get historical results from UK horseracing.

 
Answer #12    Answered By: Jordan Watson     Answered On: Jan 31

It works!!! And on the first try, too. I need to play around with it
a bit to figure it all out, but I cannot thank you enough!!

 
Answer #13    Answered By: Evan Jones     Answered On: Jan 31

You can develop your application with Excel as long as you plan for the column
expansions in the VBA code.
You can use the form feature in Excel to be able to enter data  into your
database.

I am a MS Access developer so I would tend to use Access rather than Excel.
That being said I have development some fairly complex Excel applications using
VBA and forms.

My thoughts are you can try the Excel ... develop your database  and using VBA to
do your analysis. If you can not get it to work  a lot of your efforts will not
be lost because they can be copied to Access and you move forward from there.

 
Answer #14    Answered By: Ethan Bouchard     Answered On: Jan 31

You put my mind at ease with the statement that if I need to move up
in the future my excel  stuff is largely transferrable to Access.

I'm not really sure what you mean by "planning for the column
expansions in the VBA code". David Smart has suggested named ranges,
column headings, and lookups--all of which I am investigating. Do you
have anything else in mind, or should those 3 do the trick?

I enter all of my data  into the database  by copy/paste off web sites.
When you say that I "can use the form feature in Excel to be able to
enter data into the database" I'm not sure what you mean. Are you
referring to manual data entry? Please elaborate briefly.

 
Answer #15    Answered By: Rose Hughes     Answered On: Jan 31

Sorry, I'd use a database  - probably MS Access because it has pretty good
development tools for small-scale projects.

Re WebQueries. Don't know, sorry. I've never really used them.

 
Answer #16    Answered By: Jessica Brown     Answered On: Jan 31

Two votes for Access--Looks like it's time to get an "Access for
Dummies" book to peruse in my leisure moments .

 
Answer #17    Answered By: Tia Hughes     Answered On: Jan 31

Don't set a huge store by what we'd do. I can't remember who the other "vote"
was from, but I am a software professional, with many years of experience with
all types of systems, including big database  systems. Use what you think you'll
be more comfortable with.

But ... get that Access book and have a look. You might take to it right away.

 
Answer #18    Answered By: Zachary Bouchard     Answered On: Jan 31

Just do a Google search. I'm sure there is lots of information there.
However, don't use the term "macro" in your search, use "VBA". Macros are
VBA subroutines.

 
Didn't find what you were looking for? Find more on Database Overview Question Or get search suggestion and latest updates.




Tagged: