Excel or SQL ?

Post Reply
cyxstudio
Posts: 297
Joined: Sun Mar 23, 2014 11:18 pm

I know most people uses excel but i just did written a few codes on an excel sheet that holds around 300k rows of short distance horse race data and the excel hang for 1 and a half days before giving me the output i want.

I am thinking of switching to SQL but then most betfair price data comes in excel...

is it possible to migrate excel to SQL without much trouble?

just seeking opinions
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

Yes it's possible. It's all under 'data connections' i think. Don't ask me how to do it because I don't know...but it is do-able.
I've been meaning to do the same myself too.
LinusP
Posts: 1871
Joined: Mon Jul 02, 2012 10:45 pm

Yep, depends on the engine you use on how to do it. MS SQL Server express is the easiest to use / number of features, don't waste your time with access as it doesn't scale and will just make you really angry.
Blackmoor
Posts: 26
Joined: Sat Mar 10, 2012 10:42 am

A lot will depend on you skill set, and how much effort you want to put it, but it's all very doable.

I have a various captures of BF market data which are saved to csv files, which I then load into SQL via an automated process each evening. I use SQL Server Integration Services (SSIS) to move the data, though there are many other ways of uploading.

I love Excel, but the advantage of putting it in SQL, is that you build up a warehouse, and have all your data in one place, rather than 100s of workbooks. Also it's often a lot faster on large data sets. It's then easy to backup, run reports and mine data against it. Everything goes in the data warehouse - market data, bet history, race calendars, bot definitions etc.

For some analytics, I actually link Excel sheets back to the SQL warehouse (via data connections), and then use Excel - normally for some maths that SQL struggles with (or rather I do), or to produce charts.

Hope this helps.

Cheers

Steve
wilf
Posts: 289
Joined: Fri Nov 04, 2016 7:34 pm

cyxstudio wrote:
Mon May 01, 2017 4:05 pm

is it possible to migrate excel to SQL without much trouble?
There's an addon MySQL for Excel which makes it easy to import, export and edit data. I would definitely recommend using MYSQl for large data. I still use excel, but have moved all my data to a MYSQL server because It's quicker and I can automate things.

Here's a link to the addon https://www.mysql.com/why-mysql/windows/excel/

And a tutorial https://www.youtube.com/watch?v=E_kn71R9BL0
welshboy06
Posts: 165
Joined: Wed Mar 01, 2017 2:06 pm

wilf wrote:
Sun May 21, 2017 7:32 pm
cyxstudio wrote:
Mon May 01, 2017 4:05 pm

is it possible to migrate excel to SQL without much trouble?
There's an addon MySQL for Excel which makes it easy to import, export and edit data. I would definitely recommend using MYSQl for large data. I still use excel, but have moved all my data to a MYSQL server because It's quicker and I can automate things.

Here's a link to the addon https://www.mysql.com/why-mysql/windows/excel/

And a tutorial https://www.youtube.com/watch?v=E_kn71R9BL0
I'm still wrestling with the idea myself. I started off with CSV files, then moved over to sqlite (Using python) and am now changing again to json. My database was growing way faster than I could manage. So i'm hoping that having multiple complressed json files will be the best solution (at least for the short term, shouldn't be much hassle to write a script to import it all into sql tables when the time comes)

Do you find storage an issue? or is your data on a scalable cloud environment?
Aaliyah
Posts: 1
Joined: Tue Aug 08, 2017 7:20 am

In my view MS SQL Server is much better and moving your analysis into SQL will seriously speed up your workflow. :)
User avatar
Charlie
Posts: 6
Joined: Wed Oct 11, 2017 9:37 am

Excel!!
Wolf1877
Posts: 367
Joined: Fri Sep 08, 2017 10:59 am

I'm currently using Guardian/Excel/Excel VBA to capture the data I require by appending it into simple .txt files (using VBA print). From there I have a non excel process that grabs the flat files and loads the data into a local instance of MS SQL Server express on SSD (free but with a 10gb max database). I then archive the days data over into a separate machine with massive hard disk and a separate unrestricted instance of MS SQL Server.

My personal view is keep the excel VBA processes as simple as possible so as not to slow down the excel refresh itself. It should also be possible to reverse the mechanism to feed data back into excel via flat files (which can be read in VBA), for example to place back and lay instructions via the BA excel interface but with the back/lay logic running completely external to excel.
User avatar
hmsnaveen95
Posts: 4
Joined: Tue Feb 13, 2018 9:15 am
Location: Chennai, India
Contact:

Great discussion guys, It helped me too...
Post Reply

Return to “Technology”