Contents,Table of Contents, Page Contents
How to Get Stock Market Data Into Excel
In this blog post, we will learn how to get stock market data in excel. In many situation, we need share market data for further analysis to make some decisions. We have different sources for importing share price data but in every scenario, we get this data in CSV format and then copy it in some excel and this process is very time-consuming. For better time management I have created a tool for importing share price data by which we can fetch stock market data for daily, weekly, and yearly time frames. This tool uses yahoo finance as source data .
In the below snapshot of the tool, we can see that we need to provide few inputs like Symbol name which should be as per yahoo finance, the interval for which data is required, start date, and end date. After providing this information we need to just click Get Data. Data will be fetched with 7 columns.
This is to be noted that yahoo finance is taking dates in UNIX format. For which I have added two new cells besides the start and end date.
The calculation for this is =(Start/End Date – DATE(1970,1,1))*86400 .But you don’t need to worry about these calculations we just need to provide inputs from cell C2 to C5 columns. Rest VBA inside the excel will take care.
Not let us understand how this works. Refer to the below sample URL which downloads a CSV file when put in the URL bar of any web browser.
We have highlighted 4 items in the above URL which have been used as inputs in our tool. Here INFY.NS is the name of the symbol for which we are trying to retrieve the data,period1 is the start date,period2 is the end date while an interval is the time frame it could be 1d for daily data or 1mo for monthly data. I have put this in VBA and connected it with the main sheet cells C2 to C5.
I am putting a video to explain the use of this. Kindly watch it for clarification.