R - Download daily stock data, store in MySQL - Matlab, R project and Python | futures io social day trading
futures io futures trading


R - Download daily stock data, store in MySQL
Updated: Views / Replies:8,336 / 30
Created: by Big Mike Attachments:20

Welcome to futures io.

(If you already have an account, login at the top of the page)

futures io is the largest futures trading community on the planet, with over 90,000 members. At futures io, our goal has always been and always will be to create a friendly, positive, forward-thinking community where members can openly share and discuss everything the world of trading has to offer. The community is one of the friendliest you will find on any subject, with members going out of their way to help others. Some of the primary differences between futures io and other trading sites revolve around the standards of our community. Those standards include a code of conduct for our members, as well as extremely high standards that govern which partners we do business with, and which products or services we recommend to our members.

At futures io, our focus is on quality education. No hype, gimmicks, or secret sauce. The truth is: trading is hard. To succeed, you need to surround yourself with the right support system, educational content, and trading mentors – all of which you can find on futures io, utilizing our social trading environment.

With futures io, you can find honest trading reviews on brokers, trading rooms, indicator packages, trading strategies, and much more. Our trading review process is highly moderated to ensure that only genuine users are allowed, so you don’t need to worry about fake reviews.

We are fundamentally different than most other trading sites:
  • We are here to help. Just let us know what you need.
  • We work extremely hard to keep things positive in our community.
  • We do not tolerate rude behavior, trolling, or vendors advertising in posts.
  • We firmly believe in and encourage sharing. The holy grail is within you, we can help you find it.
  • We expect our members to participate and become a part of the community. Help yourself by helping others.

You'll need to register in order to view the content of the threads and start contributing to our community.  It's free and simple.

-- Big Mike, Site Administrator

Reply
 20  
 
Thread Tools Search this Thread
 

R - Download daily stock data, store in MySQL

  #21 (permalink)
Site Administrator
Manta, Ecuador
 
Futures Experience: Advanced
Platform: My own custom solution
Favorite Futures: E-mini ES S&P 500
 
Big Mike's Avatar
 
Posts: 46,240 since Jun 2009
Thanks: 29,353 given, 83,234 received

Thanks wh, but I moved well beyond this now, you can see here:

https://futures.io/elite-automated-trading/29764-big-mike-s-custom-trading-platform.html

But I am sure your code could benefit people.

Mike

Due to time constraints, please do not PM me if your question can be resolved or answered on the forum.

Need help?
1) Stop changing things. No new indicators, charts, or methods. Be consistent with what is in front of you first.
2) Start a journal and post to it daily with the trades you made to show your strengths and weaknesses.
3) Set goals for yourself to reach daily. Make them about how you trade, not how much money you make.
4) Accept responsibility for your actions. Stop looking elsewhere to explain away poor performance.
5) Where to start as a trader? Watch this webinar and read this thread for hundreds of questions and answers.
6)
Help using the forum? Watch this video to learn general tips on using the site.

If you want
to support our community, become an Elite Member.

Reply With Quote
 
  #22 (permalink)
Elite Member
Stockholm
 
Futures Experience: Advanced
Platform: NinjaTrader,Sierra Chart, Python
Broker/Data: IB
Favorite Futures: ES & Stocks
 
Posts: 86 since Apr 2013
Thanks: 80 given, 71 received


Big Mike View Post
The column names are in the R code. If you are asking for a SHOW CREATE TABLE, I can't help any longer, I am many iterations past what I posted here. I am using IQFeed for my data and not Yahoo, so my column names wouldn't help you anyway. Just look at a data frame and then duplicate the column names.

The main MySQL thread is here, lots of discussion about integration with R towards the end of the thread:
https://futures.io/elite-circle/21664-using-mysql-storing-tick-data.html

But the main thread for my work in this area is here now, I moved it to a single thread to keep it easier from being scattered all over:
https://futures.io/elite-automated-trading/29764-big-mike-s-custom-trading-platform.html

Mike

Hi Mike,
It has passed one year since this post so I guess a lots of stuff have happen.

Do you use R to pull down all your data to Maria DB or do you use Python for this work?

Reply With Quote
 
  #23 (permalink)
Site Administrator
Manta, Ecuador
 
Futures Experience: Advanced
Platform: My own custom solution
Favorite Futures: E-mini ES S&P 500
 
Big Mike's Avatar
 
Posts: 46,240 since Jun 2009
Thanks: 29,353 given, 83,234 received



donedge View Post
Hi Mike,
It has passed one year since this post so I guess a lots of stuff have happen.

Do you use R to pull down all your data to Maria DB or do you use Python for this work?

R. See my main custom platform thread.

Mike

Due to time constraints, please do not PM me if your question can be resolved or answered on the forum.

Need help?
1) Stop changing things. No new indicators, charts, or methods. Be consistent with what is in front of you first.
2) Start a journal and post to it daily with the trades you made to show your strengths and weaknesses.
3) Set goals for yourself to reach daily. Make them about how you trade, not how much money you make.
4) Accept responsibility for your actions. Stop looking elsewhere to explain away poor performance.
5) Where to start as a trader? Watch this webinar and read this thread for hundreds of questions and answers.
6)
Help using the forum? Watch this video to learn general tips on using the site.

If you want
to support our community, become an Elite Member.

Reply With Quote
 
  #24 (permalink)
Elite Member
Stockholm
 
Futures Experience: Advanced
Platform: NinjaTrader,Sierra Chart, Python
Broker/Data: IB
Favorite Futures: ES & Stocks
 
Posts: 86 since Apr 2013
Thanks: 80 given, 71 received


Big Mike View Post
R. See my main custom platform thread.

Mike

Hi Mike,
Then I will focus on learning R and MariaDB then.

Is this the package you uses to communicate between R and mariaDB?
http://cran.r-project.org/web/packages/RMySQL/index.html


Last edited by donedge; November 20th, 2014 at 06:43 PM.
Reply With Quote
 
  #25 (permalink)
Elite Member
Stockholm
 
Futures Experience: Advanced
Platform: NinjaTrader,Sierra Chart, Python
Broker/Data: IB
Favorite Futures: ES & Stocks
 
Posts: 86 since Apr 2013
Thanks: 80 given, 71 received


donedge View Post
Hi Mike,
Then I will focus on learning R and MariaDB then.

Is this the package you uses to communicate between R and mariaDB?
CRAN - Package RMySQL

I got this error when I tried to install it. Any clue?

 
Code
(as ‘lib’ is unspecified)
* installing *source* package 'RMySQL' ...
** package 'RMySQL' successfully unpacked and MD5 sums checked
checking for $MYSQL_HOME... not found... searching registry...

cygwin warning:
  MS-DOS style path detected: C:/PROGRA~1/R/R-31~1.2/bin/x64/Rscript
  Preferred POSIX equivalent is: /cygdrive/c/PROGRA~1/R/R-31~1.2/bin/x64/Rscript
  CYGWIN environment variable option "nodosfilewarning" turns off this warning.
  Consult the user's guide for more details about POSIX paths:
    http://cygwin.com/cygwin-ug-net/using.html#using-pathnames
Error in readRegistry("SOFTWARE\\MySQL AB", hive = "HLM", maxdepth = 2) : 
  Registry key 'SOFTWARE\MySQL AB' not found
Execution halted

Warning: running command 'sh ./configure.win' had status 1
ERROR: configuration failed for package 'RMySQL'
* removing 'C:/Users/Isakinho/Documents/R/win-library/3.1/RMySQL'
Warning in install.packages :
  running command '"C:/PROGRA~1/R/R-31~1.2/bin/x64/R" CMD INSTALL -l "C:\Users\Isakinho\Documents\R\win-library\3.1" "C:/Users/Isakinho/Dropbox/Isak/Trading/R/packages/RMySQL_0.9-3.tar.gz"' had status 1
Warning in install.packages :
  installation of package ‘C:/Users/Isakinho/Dropbox/Isak/Trading/R/packages/RMySQL_0.9-3.tar.gz’ had non-zero exit status

Reply With Quote
 
  #26 (permalink)
Elite Member
Stockholm
 
Futures Experience: Advanced
Platform: NinjaTrader,Sierra Chart, Python
Broker/Data: IB
Favorite Futures: ES & Stocks
 
Posts: 86 since Apr 2013
Thanks: 80 given, 71 received


donedge View Post
I got this error when I tried to install it. Any clue?

 
Code
(as ‘lib’ is unspecified)
* installing *source* package 'RMySQL' ...
** package 'RMySQL' successfully unpacked and MD5 sums checked
checking for $MYSQL_HOME... not found... searching registry...

cygwin warning:
  MS-DOS style path detected: C:/PROGRA~1/R/R-31~1.2/bin/x64/Rscript
  Preferred POSIX equivalent is: /cygdrive/c/PROGRA~1/R/R-31~1.2/bin/x64/Rscript
  CYGWIN environment variable option "nodosfilewarning" turns off this warning.
  Consult the user's guide for more details about POSIX paths:
    http://cygwin.com/cygwin-ug-net/using.html#using-pathnames
Error in readRegistry("SOFTWARE\\MySQL AB", hive = "HLM", maxdepth = 2) : 
  Registry key 'SOFTWARE\MySQL AB' not found
Execution halted

Warning: running command 'sh ./configure.win' had status 1
ERROR: configuration failed for package 'RMySQL'
* removing 'C:/Users/Isakinho/Documents/R/win-library/3.1/RMySQL'
Warning in install.packages :
  running command '"C:/PROGRA~1/R/R-31~1.2/bin/x64/R" CMD INSTALL -l "C:\Users\Isakinho\Documents\R\win-library\3.1" "C:/Users/Isakinho/Dropbox/Isak/Trading/R/packages/RMySQL_0.9-3.tar.gz"' had status 1
Warning in install.packages :
  installation of package ‘C:/Users/Isakinho/Dropbox/Isak/Trading/R/packages/RMySQL_0.9-3.tar.gz’ had non-zero exit status

@Big Mike,
How did you get it to work, if I understand the error correct it is looking for the MySQL server, but with MariaDB it has a different path in Windows. Does this package work with MariaDB?

I have added a User variable "MYSQL_HOME" and added the paths: C:\Program Files\MariaDB 10.0\bin;C:\Program Files\MariaDB 10.0;C:\Program Files\MariaDB 10.0\include\mysql in Environments in System in Windows.

Please help!

Reply With Quote
 
  #27 (permalink)
Site Administrator
Manta, Ecuador
 
Futures Experience: Advanced
Platform: My own custom solution
Favorite Futures: E-mini ES S&P 500
 
Big Mike's Avatar
 
Posts: 46,240 since Jun 2009
Thanks: 29,353 given, 83,234 received

If you are not a proficient computer person and extremely technical with a background in programming, this is not the path for you.

Sent from my phone

Due to time constraints, please do not PM me if your question can be resolved or answered on the forum.

Need help?
1) Stop changing things. No new indicators, charts, or methods. Be consistent with what is in front of you first.
2) Start a journal and post to it daily with the trades you made to show your strengths and weaknesses.
3) Set goals for yourself to reach daily. Make them about how you trade, not how much money you make.
4) Accept responsibility for your actions. Stop looking elsewhere to explain away poor performance.
5) Where to start as a trader? Watch this webinar and read this thread for hundreds of questions and answers.
6)
Help using the forum? Watch this video to learn general tips on using the site.

If you want
to support our community, become an Elite Member.

Reply With Quote
 
  #28 (permalink)
Elite Member
Stockholm
 
Futures Experience: Advanced
Platform: NinjaTrader,Sierra Chart, Python
Broker/Data: IB
Favorite Futures: ES & Stocks
 
Posts: 86 since Apr 2013
Thanks: 80 given, 71 received

I just installed Ubuntu and MySQL instead so I hope the RMysql package will work better.

Reply With Quote
 
  #29 (permalink)
Elite Member
Stockholm
 
Futures Experience: Advanced
Platform: NinjaTrader,Sierra Chart, Python
Broker/Data: IB
Favorite Futures: ES & Stocks
 
Posts: 86 since Apr 2013
Thanks: 80 given, 71 received


Big Mike View Post
Final version (likely) for this thread

Mike

Hi @Big Mike,

I have tried to write to the database, but I get this error:

 
Code
> # Big Mike Trading
> # www.bigmiketrading.com
> # November 2013
> # v1.0.5
> 
> # Init
> #rm(list=ls())
> 
> # Market Cap threshold ($) to be included in meta data table
> mcapthresh = 1000000000
> 
> # SQL table name for meta data in db
> tblmeta = "meta"
> 
> # SQL database name for Yahoo data
> dbyahoo = "daily"
> 
> # SQL database name for FRED data
> dbfred = "fred"
> 
> percent <- function(x, digits = 0, format = "f", ...){
+   paste(formatC(100 * x, format = format, digits = digits, ...), "%", sep ="")
+ }
> 
> tic <- proc.time()
> 
> source("mysqlData.R")
> source("downloadData.R")

Fetching all stock tickers

Updating meta data for all tickers ...

Error in if (df[i, ]$MarketCap > 0) { : 
  missing value where TRUE/FALSE needed
> 
> cat("\n\n",proc.time() - tic)


 3.4 0.63 12.71 NA NA
Do you have any idea?

Thank you in advance!

Attached Thumbnails
R - Download daily stock data, store in MySQL-mariadb.png  
Attached Files
Register to download File Type: r mysqlData.R (3.0 KB, 13 views)
Register to download File Type: r downloadData.R (3.5 KB, 12 views)
Register to download File Type: r init.R (602 Bytes, 11 views)
Reply With Quote
 
  #30 (permalink)
Elite Member
tel aviv
 
Futures Experience: Advanced
Platform: Multicharts, SC
Favorite Futures: es
 
Posts: 34 since Jun 2009
Thanks: 4 given, 1 received


thanks for this tool.
do you have any idea where and how i can download historical equities earnings dates? i just need the date.
same thing for future earnings days?

thanks

Reply With Quote

Reply



futures io > > > > R - Download daily stock data, store in MySQL

Thread Tools Search this Thread
Search this Thread:

Advanced Search



Upcoming Webinars and Events (4:30PM ET unless noted)

Jigsaw Trading: TBA

Elite only

FuturesTrader71: TBA

Elite only

NinjaTrader: TBA

Jan 18

RandBots: TBA

Jan 23

GFF Brokers & CME Group: Futures & Bitcoin

Elite only

Adam Grimes: TBA

Elite only

Ran Aroussi: TBA

Elite only
     

Similar Threads
Thread Thread Starter Forum Replies Last Post
Using MySQL for storing tick data Big Mike The Elite Circle 131 October 3rd, 2016 04:09 PM
R and MySQL for tick data Big Mike Matlab, R project and Python 3 November 10th, 2013 12:08 PM
Looking for Software to store Price data in MySQL caliph Matlab, R project and Python 4 January 7th, 2013 03:11 PM
Store real time data from NT in mysql bascher NinjaTrader 23 November 4th, 2011 06:53 AM
PLT_NTDataCollect store bid ask tick data to its local database? rcabri Trading Reviews and Vendors 3 December 27th, 2010 11:00 AM


All times are GMT -4. The time now is 11:50 AM.

Copyright © 2017 by futures io, s.a., Av Ricardo J. Alfaro, Century Tower, Panama, +507 833-9432, info@futures.io
All information is for educational use only and is not investment advice.
There is a substantial risk of loss in trading commodity futures, stocks, options and foreign exchange products. Past performance is not indicative of future results.
no new posts
Page generated 2017-12-15 in 0.12 seconds with 20 queries on phoenix via your IP 54.226.34.209