Smartgambler
Pro-Punter

Go Back   OZmium Sports Betting and Horse Racing Forums > Public Forums > General Topics
User Name
Password
Register FAQ Search Today's Posts Mark all topics as read

To advertise on these
forums, e-mail us.

Reply
 
Thread Tools Search this Thread Display Modes
  #1  
Old 18th May 2010, 12:11 PM
foggy foggy is offline
Member
 
Join Date: Jan 1970
Location: South West Sydney
Posts: 37
Default "Humungous excel spreadsheet as database" problem

I am a constant bettor of soccer and have been using Excel as a database for the last 4 years. I have results and stats for over 71,000 games. This is not a problem but I have another spreadsheet that assesses certain selections and then calculates all sorts of stats across the entire width of the spreadsheet ( to column "IV") and down 10,000 lines, which is only enough to get me through to the end of this year.

Originally these two spreadsheets were in the same file (size 77mb) but I have had to split them up so that this assessment sheet is by itself (size 52mb).

There are a few other files that this one is linked to and this large one takes the best part of 2 minutes to open even though I have a 3.62 GHz processor.

With a file this large it takes a while for it to re-calc after each piece of data entry (approx 20 - 30 secs) in each cell, unless I turn "auto-calc" off and re-do at the end.

I am currently 3 and half weeks into betting the best selections that the stats that this "database" has revealed for upcoming games and have obtained a strike rate of 76.92% at $1.6372 avg div (26 sel/20 wins) in this time.

From these stats, I am keen to keep it going, and going well and fast, and for longer than one year.

The question I need to ask is, am I better off using Access or this SQL I have heard about?

I do not know anything about SQL but every attempt that I have made in the past to use Access leaves me dumbfounded. I find it very hard to learn and, unfortunately, do not have it on this PC.

Anyone's experience and suggestions in this matter would be greatly appreciated.
Reply With Quote
  #2  
Old 24th May 2010, 01:40 PM
foggy foggy is offline
Member
 
Join Date: Jan 1970
Location: South West Sydney
Posts: 37
Default

I guess this one would have been better off posted on the Microsoft Community website for Access and Excel. Probably no one on this site uses SQL to be able to make an informed suggestion to my query.

Thanks to anyone who may have given it thought.
Reply With Quote
  #3  
Old 24th May 2010, 02:48 PM
lomaca lomaca is offline
Member
 
Join Date: Jan 1970
Posts: 1,096
Default

Quote:
Originally Posted by foggy
I guess this one would have been better off posted on the Microsoft Community website for Access and Excel. Probably no one on this site uses SQL to be able to make an informed suggestion to my query.

Thanks to anyone who may have given it thought.
SQL is basically a query language that you can use on databases.

Excel is inherently slow once you get over a certain data size and while it's attractive because it's relatively easy to get going, one soon finds out the limitations.

Access is an alternative as are half dozen other databases, but you don't have to use the Access programming language to manipulate the data.
If you can use Excel (VBA) language you are half way there, you can use Visual Basic or VB.NET or any other programming language in conjunction with SQL, while Access is acting as a mere repository of your data.

I think there is even a free database available with Open Office.

True, the learning curve may be steep, but your other options are limited, the best you could do is to break up your sheets further into smaller data sheets, based on dates or locations and link them.
Whichever way you go it will take some learning.

Good luck
Reply With Quote
  #4  
Old 24th May 2010, 03:28 PM
Crackone Crackone is offline
Member
 
Join Date: Apr 2006
Posts: 368
Default

Hi Foggy if I have a problem with excel I look at this site,

http://www.mrexcel.com/forum/index.php
Reply With Quote
  #5  
Old 25th May 2010, 11:01 AM
foggy foggy is offline
Member
 
Join Date: Jan 1970
Location: South West Sydney
Posts: 37
Default

Thanks guys, both suggestions were great.

The splitting of the file down to just that one sheet has improved it quite a bit but I am hesitant to have dates split up as I would then have to open numerous files, say by the month, if I wanted to add in some new parameter to assess. It currently calculates assorted "form selection methods" for the last 4000 contenders back to Jan 1 this year. If this was the case of breaking it up I would then have to open every month separated and input each new assessment criteria formula by column "copy and paste". This would make assessment slow and cumbersome when I have a new idea.

I think the Open Office way to go might be the thing, if it has an Access style database program. Will have a look.

The MrExcel website, looking like a copy of this website, may give rise to a few solutions, as well.

Many thanks for the help.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is On
Forum Jump



All times are GMT +10. The time now is 08:29 PM.


Powered by: vBulletin Version 3.0.3
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
©2008 OZmium Pty. Ltd. All rights reserved . ACN 091184655