Create your own Finance DataBase with Python & SQL [perfect for backtesting trading strategies]
HTML-код
- Опубликовано: 20 июн 2024
- Hi everyone,
this video shows how you can create your own finance database using Python and SQL.
This video is important as I might(!) use my own SQL databases in future trading strategies and I can refer to this video where I got my data from.
Get the Notebook/Source code by becoming a Tier-2 Channel member:
/ @algovibes
As said in the video if you are using MySQL / MS SQL Server / Postgres instead of the create engine function you need to create a schema. Let me know if you need support with that.
Please subscribe and like this video if this was helpful. Thank you very much in advance!
Python and SQL videos:
basics:
• How to connect Python ...
more importantly:
• Pandas Dataframes and ...
0:00 - 04:55 Getting and preparing the data with yfinance
04:55 - 06:50 Create the database engine (SQLITE! Please listen to my comments or read video description for other DB systems)
06:50 - 10:45 Transfer the data to the SQL database
10:45 - 11:20 Taking a look at the Database (SQLite browser is used)
11:20 - 14:01 Requesting data from the database
![SQL Tutorial [MySQL] for BEGINNERS in under ONE hour](http://i.ytimg.com/vi/eB9byZhy3xA/mqdefault.jpg)
![SQL Tutorial [MySQL] for BEGINNERS in under ONE hour](/img/tr.png)
![How to Manage a Database with Python, Pandas & SQL - UPDATE a DB professionally [DO NOT SKIP THIS!]](http://i.ytimg.com/vi/WqXknI1kql8/mqdefault.jpg)
![Introduction to BACKTRADER [Backtesting Trading Strategies Library for Python]](/img/1.gif)
This is probably one of the best channels on RUclips period. It's value per content is off the charts. Thanks AlgoVibes for creating high quality content. RUclips needs more people like you. 👍
Thank you so much Javier!
Your work is awesome. You provide true quality content. Thank you
Thank you very much for your comment. Really appreciate it :-)
I spent hours trying to connect a sqlite database with python!! and you did it in minutes! THANK U!!!!!!!
Very welcome :-)
I've really been enjoying the tutorials. They are insightful & very well explained! Kudos!
Thanks a lot Ro. Really appreciate your comment!
Great videos man, to the point and easy to follow!
Hey man, thanks a lot for your comment. Happy to read that!
Thanks and good Sunday!
Thank you. Same for you! :-)
Great video, really helped me in storing data. Please continue the good work and I will make sure to like and comment .
Thanks a lot for your support buddy (:
You are truly incredible my friend, changed my day, incredible tutorial.👏👏👏👏👏
Thanks a lot buddy :) Appreciate your comment
Great job. Go on
Thanks for your support :-)
I was searching for Python for Finance videos anyhting related to Yves Hilpisch.. And this is what I got..I am a Finance guy and few weeks ago started learning python and really having fun by learning new techniques..Thankyou for such awesome tutorials..❤ from India..
My best greetings to India🇮🇳 :-) Be invited to check out my Python for Finance playlist. You will find a lot of useful stuff there. Let me know if there is anything you are looking for in specific!
That was amazing.
Thanks buddy, happy to read!
Thank you, very informative! Just subscribed to your channel.
Thank you very much man! :-)
my favorite channel.
Thank you very much mate
Thanks for the video. Very concise.
Welcome mate!
Whenever we plan for such project, I would plan to update the incremental data . Instead of creating symbol-wise tables one can insert an additional column for symbol, there are both pros and cons for this approach but one is restricted to one table for a market or exchange. How to get data for new symbols added is another requirement for such project. Anyways your approach to code a solution is quite unique, take this as a compliment.
I love your videos, they are being really helpful to me! What IDE are you using on this one? I loved this color theme.
Hi man, thank you so much!
Using Jupyter Notebook here.
Bro I'm from India this was really helpful , Thanks alot bro
Awesome to hear :-) Thanks a lot and greetings to India 🇮🇳 ❤️
@@Algovibes a complement from an Indian on programming? Man, you know you are doing good things. I only ever watch Python tutorials from Indians and Germans.
great video sir, pls extend this series by adding some indicator or can we use it in live market , or run any strategy in live market
Thank you very much for your support :-) Appreciate it! I will see what I can do. Thanks for your suggestion - very helpful for me!
Im loving these vids! Just wondering, have you ever considered, or is it possible; to use generators within tables so that for instance the db could be updated and then generators will automatically calculate indicators in a seperate table within the db???
Thank you buddy, sounds doable but didn't have exposure to that yet.
This seems like the obvious next step
Thanks also for this awesome tutorial. Liked and commented however already subscribed before.
Downloading such histories is usually needed for trading system development. What still also happens to pros is they forget about the survivor bias. The effect should not be underestimated! Could you make a tutorial on how to create a survivor bias free database (which may not be so easy as some old tickers are meanwhile delistet from YF and other sources). And further you could show the difference in resaults between survivor bias free data and "the easy way data" with the index' current constituents.
Welcome! Thanks a bunch for watching and your support :-) Appreciate it.
You are absolutely right regarding the survivorship bias and this topic is kind of a painful one as it is hard to find the companies being delisted and when exactly they were. The main problem doing that is just the data availability - not the how.
You need a valid database such as the CRSP or another one usually not free of fees. I will see if I can dedicate some time on this topic but do not want to make a promise.
@@Algovibes Hi Algovibes,
I think, i have a list of companies of the s&p 500 from today back to 1996. Is this what you are looking for? How can i contact you?
def TOSQL(frames, symbols, engine):
for frame,symbol in zip(frames, symbols):
frame.to_sql(symbol, engine, index=False)
print('Successfully imported data')
Hi, thanks for nice video. I think it’s better if we can make it auto update database for new days
Thanks buddy! Be invited to check out the Stock recommendation playlist. I have covered updating a DB there. Cheers!
Awesome work!
What do you think is the main advantage of storing dfs into SQL databases instead of csv, Excel etc.?
Really looking forward to more Python / SQL combination!
Thanks a lot :-) Appreciate your kind comment.
Some advantages:
- Better overview/structure
- Direct queries possible (e.g. get all dates after 2010)
- More efficient if you are not reading in the whole table
Privately I am using MySQL btw. But I think SQLite is more lightweight. Might do some MySQL coverage tho.
@@Algovibes Can't wait to see how you'll architecture this on MYSQL
@@Algovibes I'm most concerned with speed as I want to create an option backtester. How exactly will the database help with this? Python still needs to query the database just like it would have to otherwise read_csv, right?
@@Algovibes Yes please do that (Python & MySQL). By the way, what are the pros and cons of using MySQL instead of SQLite?
excellent tutorial. Thanks for your good work. Can you please guide me on how to update the database every day with the latest price?
Thanks you very much for your comment. Appreciate it :-)
I covered that in this video:
ruclips.net/video/AuZmsv6dQCM/видео.html
I will probably also include that in Part III of the Stock recommender series.
Hi Algovibes.
Nice video.
Why does your schema store each symbol in a separate table?
The data for the symbols always have the same columns: date, open, low, high, close, adj. close, volume.
Wouldn't it be smarter to store the ticks records of all symbols extended by one column, namely the symbol name, together in a single table?
I personally wouldn't do it like that but you can do that and I would also not recommend doing that.
The video was very helpful. Can you also guide as how to update the database later on
Great to hear, thank you :-)
I covered updating in my stock recommender series but I was using MySQL there so you need to do some changes here and there:
ruclips.net/video/FkZNUj36Jcc/видео.html
Hope that's helpful!
Hi Algovibes, I have just joined the memebership. And I want to know where to download the python script mentioned in this youtube video? And how can I create MySQL database and tables to hold those stock price? Thans, Alan
Here :-)
ruclips.net/video/qGAUw63p_uk/видео.html
Very nice and good explanation thank you.
How can we daily update the same db.
Thanks a lot for watching and your comment :-)
You can update it with a Python script containing the same logic but using the optional append argument (in the to_sql function). I am doing that privately as well. If you don't want to execute the script on your own everyday (or whatsoever time interval) you could schedule a task on your system.
👍
Thanks for your comment :-)
Cant u make a smilar video but this time instead of stock prices getting the info data or balance sheets and income statements?
Great.. I was planning to have a algo trading bot using this. Can have one such video
Thanks for your comment.
I have some videos on that. Check out my cryptobot playlist and the stock recommender playlist. Might be interesting for you.
Good video. Why you create a table per ticker? Isn't it easer to create one table for all price data for all tickers (creating a column for the ticker value)?
Thanks mate. Also possible, I think it's way better organized that way. But whatever suits best for you!
CAN U DO CODING FOR NSE INDIA, 1 minute data for Nifty and options into Mysql server database?
Hello again. I had 2 questions with your permission. You wrote in the Momentum video that we can capture the data this way. So how do we combine the two together? My second question is, I want to trade BIST30 (XU030) shares traded in Borsa Istanbul, but I could not retrieve the data again because detailed information such as dow or sensex is not available on this site. Is there any alternative to this? If so, is it possible to combine your new video with the Momentum video and make it over the XU030?
Hi man,
always happy to take questions!
Can you elaborate on what do you want to combine? Please understand that I meanwhile did a lot of videos and don't have an exact overview what I was covering where in detail.
Second question is a very interesting one. Ad hoc I would suggest something like web scraping the information. I didn't have exposure to the Turkish market yet (although I am observing some companies due to weak lira) and don't have good data sources at hand.
@@Algovibes In the video I shared the link below, we see that the components tab in yahoo finance no longer works. I wanted to ask how we can apply the data extraction process in this video to that software.
ruclips.net/video/dnrJ4zwCADM/видео.html&lc=UgwZZzbJgbI8F0-o7PZ4AaABAg.9WGZP3Wu_1_9WKbVJGO7nJ
Have you tried the S&p500 from wikipedia? That gives an index error. While the Dow works fine.
Was using the S&P in one of my newer videos and worked fine. Check it out and double check if possible!
great video with best optimal content
can u make an video where we can able to calculate the index by our own
Hi mate,
thank you so much!
Could you elaborate on your question? thx!
there is a certain formula to calculate index by collecting all the 50 stock data and applying regression algorithm separately and using the index formula to calculate index.
by doing this method by any chance can we able to get more accuracy?
fantastic work . I do have some question though.
1> How to update data daily basis?
2> is there way we can fetch NSE option chain.
Hi mate, thanks a lot for your comment.
I covered updating here: ruclips.net/video/AuZmsv6dQCM/видео.html
Regarding option chain:
yfinance offers this feature. Just create an object for a particular stock like so:
aapl = yf.Ticker("AAPL")
aapl.option_chain('2021-07-09').calls
Probably will cover stuff like that in a future video.
@@Algovibes What's the meaning of "option_chain" please ?
@@ihebbibani7122 www.investopedia.com/terms/o/optionchain.asp ;)
can u show us how to get not only stock prices into mysql but also fundamental data like pe ratio, dividend yield or information in infos in general?
Actually did it in my live stream. Was working with sqlite tho but it's a similar way. Be invited to check it out:
ruclips.net/video/TJK-Ct9WnCw/видео.html
@@Algovibes Ty, I will.
@@Algovibes Ty, I watched it. Unfortunately it was not exactly, what I was looking for. I am trying to download fundamental data from yahoo finance in a fast way for a lot of tickers, 1000 or so, but I don't know how. Using yfinance takes too much time it works really slow. Once u said using sql it could be solved much quicker, may be u can do a video addressing this issue.
Hey i get this error when running the code at 10:21 :
line 31, in
TOSQL(india, tikersSensex, indiaengine)
line 27, in TOSQL
for frame, symbol in zip(frames, symbols):
TypeError: 'NoneType' object is not iterable
Could you help ?
Hi, you need to make sure frames and symbols are actually containing values. The error is just telling you that there are None values.
First of all, thank you for the informative video. I practiced the entire code on my notebook. When I tried to re run the notebook next day, I got an error In using TOSQL function. It say the table”stock name” already exists. Please help me with it.
Thanks for watching man. Please check my videos on Python and SQL. I have covered how to append data to an existing table there.
Hello,
I liked your videos and started following. In this video, I got an error message while assigning tickers variable. The error was "list index out of range". What could be the reason?. thanks.
Hi man, thanks a lot for your support. When exactly are you getting that error? Can you pass me a timestamp?
Error message= AttributeError: 'DataFrame' object has no attribute 'Symbol' . You should also teach us data reading with read.html :)
I solved it.
Hi Algovibes, do you know how to resolve this problem? Thanks.
Need a timestamp, then I am happy to help!
I am receiving a URLError: . I tried to do a pip install certifi but it appears the requirement is already satisfied. Any suggestions?
stackoverflow.com/questions/35569042/ssl-certificate-verify-failed-with-python3
def TOSQL(frames,symbols,engine):
for frame,symbol in zip(frames,symbols):
frame.to_sql(symbol,engine,index=False) showing error in this part invalid syntex
pls check
Shouldn't result in an error. There is nothing wrong with the syntax. Please provide me your error message, we will fix that together quickly.
Name error: name 'symbols' is not defined
What to do with this error
Can you pass me a timestamp where this error is occurring?
Can you create Finance DataBase with Python & Microsoft Access ?
You can but I probably won't as I don't use MS Access.
@@Algovibes Can you convert database in XBRL to SQL ?
TypeError Traceback (most recent call last)
Input In [33], in ()
----> 1 TOSQL(us_mega, symbols_mega, mega_engine)
Input In [32], in TOSQL(frames, symbols, engine)
1 def TOSQL(frames, symbols, engine):
----> 2 for frame,symbol in zip(frames, symbols):
3 frame.to_sql(symbol, engine, index=False)
4 print('Successfully imported data')
TypeError: 'Engine' object is not iterable
Hi Alan, can you pass me a timestamp where you are getting this error at? thx a lot