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
Excel or SQL ?
- 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.
I've been meaning to do the same myself too.
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
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
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
-
- Posts: 165
- Joined: Wed Mar 01, 2017 2:06 pm
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)wilf wrote: ↑Sun May 21, 2017 7:32 pmThere'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
Do you find storage an issue? or is your data on a scalable cloud environment?
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.
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.
- hmsnaveen95
- Posts: 4
- Joined: Tue Feb 13, 2018 9:15 am
- Location: Chennai, India
- Contact:
Great discussion guys, It helped me too...