When you need to download financial data to use into Power BI, searching for the right website or API can be a daunting task. Luckily, thanks to Python and its support into Power BI, it is possible to download stock data effortlessly.
Download Python
I am assuming the reader has already Python installed, if not you can head over to: https://www.python.org/downloads/ and download the latest version. Python makes it easy to perform many transformations which are not yet supported in Power Query, like for instance the use of Regular Expressions.
As a code editor, you can download Visual Studio Code: https://code.visualstudio.com/
Download Pandas
Learning Pandas is a must if you are serious about doing Data Analysis with Python. No specific knowledge is required from you in order to benefit from this post, but I highly recommend you to look into it. After having installed Python, open the cmd.exe and type:
pip install pandas
once it has been installed, type:
pip install pandas_datareader
You can find how to set up Power BI to use Python in the following article: https://docs.microsoft.com/en-us/power-bi/desktop-python-scripts
Download Stock Data
Now that we’re done with the boring stuff, let’s download some stock data. Let’s say we wanted February 2019 stock data of Microsoft, Apple and Amazon. This becomes really easy using pandas_datareader. The Python script we will run is the following:
1 2 3 4 5 6 7 8 9 | import pandas as pd import pandas_datareader as web from datetime import datetime data = (web.get_data_yahoo(symbols=['AAPL', 'AMZN', 'MSFT'], start=datetime(2019, 2, 1), end=datetime(2019,2,28)) .stack(level=1, dropna=False) .reset_index(level=[0,1], drop=False)) |
Open Power BI, go to Get Data, then choose Other and then Python Script:
Python Script Power BI
Python Script Power BI
Once you click OK and let the script run, you will have stock data available in the Power Query editor:
Python Script Power BI
you now just need to expand the table and done!
Hello, How we can import the data for ALL ‘symbols’ ?