I got a lot of complaints from people trying to download stock prices automatically from Yahoo. It's been a big issue for people, so I created a video for doing the exact same thing from Google! Here's how to automatically download stock quote data from Google Finance. The background is that Yahoo was seeing less traffic on their website because people were downloading their data without going to the website to do it. So they changed the way their site works so that you have to actually pull up the page in order to download the data. Google isn't having that problem (either because they make so much money, or because they don't care) so you can still pull the data from them for now.
Use Google Sheets’ GoogleFinance function to display data in Geckoboard. The end date when fetching historical data, or the number of days from start_date for which to return data. Essentially, what this formula is doing is querying Google Finance for GOOG current price (GOOG is the ticker symbol for Google Inc.).
Go to Google.com/Finance and get the historical prices for a stock that you want. Follow along this video to get it fixed.
By: Last Updated: 2016-10-27 Related Tips: Problem I read about the GOOGLEFINANCE function with Google Sheets as a convenient way to get historical and current data for stocks. Please demonstrate a solution for passing data for a bunch of stocks from the Google Finance site to a SQL Server database using the GOOGLEFINANCE function in Google Sheets. Solution The GOOGLEFINANCE function inside of Google Sheets downloads historical and current data on stocks and other securities from the Google Finance site. However, the downloaded data end up in a web-based spreadsheet tool instead of residing within a database like SQL Server.
This tip demonstrates a simple solution for using the GOOGLEFINANCE function in combination with T-SQL code for transferring historical data from Google Finance to a SQL Server database for data mining of stock prices and stock trading rules. The GOOGLEFINANCE function inside of Google Sheets is attractive because it can download historical data for multiple stock tickers within a single spreadsheet. Get an introduction to this function. After specifying a Google Sheet for a bunch of stock symbols, you can share that sheet with others. In contrast, both Google Finance and Yahoo Finance sites offer an URL with query string parameters API for specifying what stocks and time period to download data. However, the URL with query string parameters approach allows the downloading of data for just one ticker symbol at a time. Additionally, the downloaded data with the URL with query strings parameters approach does not facilitate data sharing with the same ease as Google Sheets.
This tip presents a solution for downloading historical data for 60 ticker symbols for data going back as far as January 2006 and then imports the data to SQL Server. It is very straightforward to change the ticker symbols as well as the date range for which data is downloaded. The presentation starts with an overview of the solution strategy. Later, selected features of the solution are examined to empower you to expand and modify the solution as your needs dictate. The presentation of the solution's technology also includes an examination of selected data quality issues for historical stock prices from the Google Finance site.
An overview of the solution strategy Ignoring installation and setup issues for Google software, there are two main parts of the solution. The first part handles the download of historical stock data from the Google Finance site to your computer - one file per ticker symbol. The second part imports the downloaded files to a SQL Server table Since the problem addressed by this tip is to download data from the Google Finance site with the GOOGLEFINANCE function within Google Sheets, you need access to Google Sheets. Furthermore, Google Drive is currently the only means of managing sheets created with Google Sheets and files derived from those sheets. The functionality provided by Google Sheets and Google Drive also requires the Google Chrome browser and a Google account to the overall Google site.
Access to all of this software is available free from Google, and you can install Google Chrome as a non-default browser so that you can continue to use Internet Explorer as your default browser (if that is what you prefer). For your convenience, the links for getting to use Google Chrome, Google Sheets, and Google Drive appear below. As you setup access to Google Sheets and Google Drive, you will receive prompts about setting up a Google account if you do not already have one. Google Chrome:. Google Sheets:. Google Drive: A demonstration on downloading the data from Google Chrome Once you have your computer configured with the application environment referenced by the solution, you can open three Google Sheets files that download the data for this solution demonstration.
The sheet names (stocksforminingA2A25, stocksforminingA26A49, and stocksforminingA50A61) and their URL links appear below. Link to share for: stocksforminingA2A25 link to share for: stocksforminingA26A49 link to share for: stocksforminingA50A61 Each sheet has two tabs with control information about the range of ticker symbols and dates for which to download data. The remaining tabs within each sheet contain the historical stock price data. The stocksforminingA2A25 sheet has data for ticker symbols in cells A2 through A25 within its tickerlist tab. The following screen shot shows the sheet with the tickerlist tab selected and the cells A2 through A25 highlighted. The tab named startandenddates has values of 1/1/2006 for a start date and 9/24/2006 for an end date. Historical stock price data are end-of-day data.
You should add one day to the last day through which you seek to download data. Therefore, if you want end-of-day data through September 23, 2016, you should specify September 24, 2016 as your end date. You can modify the ticker symbols on the tickerlist tab as well as the dates on the startandenddates tab to change the range of dates and symbols for which historical data is downloaded in the stocksforminingA2A25 sheet. Furthermore, the other two sheets (stocksforminingA26A49 and stocksforminingA50A61) download historical data for ticker symbols in cells A26 through A49 and cells A50 through A61 of the tickerlist tab. There are 60 cells from A2 through A61, and downloaded data is for these symbols across the three sheets. The next screen shot shows the A2fromtickerlist tab within the stocksforminingA2A25 sheet. There are 23 additional tabs within the sheet with names like A3fromtickerlist through A25fromtickerlist.
Notice that cell A1 is selected within the A2fromtickerlist tab. Within the function bar (fx), you can see an instance of the GOOGLEFINANCE function syntax for recovering historical data. For the current solution, the function populates cells in columns A through F. You can export the data from any tab within the stocksforminingA2A25 sheet as a csv file with the File, Publish to the web menu item.
This menu selection publishes the content of a designated tab in a sheet to Google Drive. From Google Drive, you can share the published file and/or download it to your computer. The solution described by this tip uses the latter option so that the data can ultimately be imported into SQL Server. The following screen shot shows the dialog settings for publishing to Google Drive the contents of the A2fromtickerlist tab as a csv file.
Notice that you can use selection boxes for designating both the tab name and format of the published file. Clicking the Publish button launches the process.
You can use the File, Publish to the web menu command to publish successively the contents of each of the tabs in a sheet. Because of the structure of the sheets in this tip, the csv files are not exactly like many csv files. In particular, there is a jagged right edge to the file because the ticker symbol value appears only on the initial row. Custom T-SQL code for importing the csv files downloaded from Google Drive into SQL Server can assign a ticker symbol value for each row in a ticker's data set so that the right edge is no longer jagged within a SQL Server table. You might be wondering why there are not 60 tabs for 60 ticker symbols in a single sheet. The answer is that there is an upper limit on the number of cells within a single Google sheet.
For the data in this tip, this limit is reached when trying to load the 25th tab of historical stock data within a sheet. Therefore, the first two sheets (stocksforminingA2A25 and stocksforminingA26A49) each have 24 tabs with historical stock data, and the third sheet (stocksforminingA50A61) has an additional 12 tabs containing historical prices and volumes for the remaining 12 ticker symbols in a set of 60 ticker symbols. An overview of the importing part of the solution via a T-SQL script A T-SQL script file uses 4 main tables and a WHILE loop to convert the downloaded csv files for each stock ticker symbol for insertion into a single table in a SQL Server database.
Create temporary sequence in #T with identity #s - for each row in WHILELOOPFIELDS if OBJECTid('tempdb.#T') IS NOT NULL DROP TABLE #T SELECT Identity(int, 1,1) AS PK Into #T FROM WHILELOOPFIELDS - set variables for importing each data file - and adding symbols to consolidated table Declare @maxPK int;Select @maxPK = MAX(PK) From #T Declare @pk int;Set @pk = 1 DECLARE @SYMBOL varchar(4), @pathfilename varchar(100) DECLARE @sql varchar(1000) Here's the T-SQL code to manage the operation of the WHILE loop with comments inside of the WHILE loop to indicate key operations within the loop. The code for the operations inside the loop will be covered subsequently. You can see that the code for managing the loop is very straightforward. Loop through rows with stock symbols and - data file paths and names WHILE @pk. Use pathfilename column value on the row to bulk insert - csv file for stock symbol to importedstockpriceslines table - start from csv file row after one with column headers SET @sql = 'BULK INSERT importedstockpriceslines ' + 'FROM ' + @pathfilename + ' ' + 'WITH ' + '(' + 'FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '0x0a' + ');' EXEC (@sql) After that, the column values are extracted from between the commas in the importedstockpriceslines table and inserted into the importedstockprices table. The custom code uses CHARINDEX functions, nested within one another as required, to extract the comma delimited values in each row within the importedstockpriceslines table. A couple of factors require this special coding for extracting commas separated values within rows of the importedstockpriceslines table to individual column values within the importedstockprices table.
Propagate SYMBOL column value of row with the earliest date - to all subsequent rows in importedstockprices - CASE.END statement omits trailing carriage return - code in raw symbol data, which marks end of line SET @SYMBOL = ( SELECT TOP 1 CASE WHEN ASCII(SUBSTRING(SYMBOL,4,1)) = 97 AND ASCII(SUBSTRING(SYMBOL,4,1)) = 97 AND ASCII(SUBSTRING(SYMBOL,3,1)) = 97 AND ASCII(SUBSTRING(SYMBOL,2,1)) = 97 AND ASCII(SUBSTRING(SYMBOL,1,1)). Populate table with consolidated ohlcv data - for all stock symbols in WHILELOOPFIELDS INSERT INTO stockssymbolohlcv ( SYMBOL, date, open, high, low, close, volume ) SELECT SYMBOL, date, open, high, low, close, volume FROM importedstockprices; Data Issues with Google Finance and Historical Data Reviewing a couple of data issues can give you a feel for the quality of historical data that you get from Google Finance as well as how validly this tip maps source data from Google sheets to a SQL Server table. The following screen shot shows the top 3 and bottom 3 rows for the drd symbol from the stockssymbolohlcv table along with the pathfilename column to identify its source tab in an underlying Google sheet.
The next pair of screen shots shows the corresponding rows from the A5fromtickerlist tab in the stocksforminingA2A25 Google sheet. Here are some data quality issues that merit your attention. The historical values map as specified between the loaded data in SQL Server and the source data in the Google Sheet tab; recall that bad data, such as #N/A for a money data type value, in a Google sheet map to a NULL value in SQL Server. This outcome for row 1 of the result set for the top three rows confirms the validity of the approach for loading data from Google Finance to a SQL Server database.
The very first row in the result set for the top three rows had bad data for at least 3 columns (open, high, and low); these bad values were converted to NULL values when loaded to SQL Server. Also, the volume column value was curious because it showed a value of 0 shares traded for the day. These findings suggest there may be Google Finance data issues that can benefit from fixes for selected dates and symbols combinations.
Here's a query for count of rows loaded by ticker symbol with the first 18 rows of the result set showing in the screen shot. Some top line conclusions from reviewing data quality issues are the following. The process described in this tip loads data into SQL Server that perfectly match the source data from Google Finance. The Google Finance site has occasional data quality issues.
The review from this tip confirms these issues are relatively rare. Data quality lapses can be repaired by referring to alternate sources, such as Yahoo Finance or other data providers of historical data.
There was no detailed data quality analysis of stock history data for alternative providers so we can say whether Google has more data quality issues than any other. However, it was possible to verify that repairs could be made to data from Google Finance based on data from Yahoo Finance. Neither Yahoo Finance nor other providers of free historical stock data have a function comparable to the GOOGLEFINANCE in Google Sheets, which may make Google Finance a preferred choice for downloading historical stock data for a bunch of ticker symbols Next Steps This tip demonstrates how to download data for a set of 60 stock ticker symbols with the help of the GOOGLEFINANCE function in Google Sheets. The tip also covers how to copy the downloaded stock history data from Google Sheets to Google Drive and ultimately to SQL Server. Here are some suggested next steps depending on your needs and interests. You can configure accounts for using Google Sheets and Google Drive, copy the importdownloadeddataintoSQLServer.sql script file from the for this tip, and confirm that you can get the tip to work on your computer.
You can use the historical data for the 60 symbols to test and explore stock trading strategies using a framework demonstrated in a prior MSSQLTips tip. The prior tip examined trading strategies for just 10 stock symbols.
Another next step is to examine ways of easily expanding the number of stock symbols for which you can easily download data. For example, you can save in a backup table the stock history data for the 60 ticker symbols in this tip, and then re-run the solution for a different set of 60 ticker symbols. This simple step will double the number of stock symbols available for stock data mining and trading strategy evaluations. Finally, you can explore ways of automating selected manual steps within the process described within this tip.
There is a scripting language for Google Sheets that can facilitate this objective The resource files folder for this tip includes the file, which contains the script for automating the importing of csv files from Google Drive to SQL Server. The resource files folder also contains a couple of other scripts used to highlight the validity of the data loaded into SQL Server as well as data issues with historical stock data from Google Finance. There are other miscellaneous files in the resource files folder that are likely to simplify any next steps that you may pursue with this tip. Therefore, please be sure and review the readme.txt file in the resource files folder before trying to reproduce any results in this tip.
![Finance Finance](http://investexcel.net/wp-content/uploads/2012/09/Google-Finance1.png)
Finally, I close by indicating that this tip is not recommending any particular stocks, including the 60 stocks used in this demonstration. Actually, the Google Sheets for this tip, which you can find by following links presented towards the top of this tip, include more than 60 stock symbols.
You may find the additional stock symbols useful for augmenting the base set that you can download from the sheets as they are. At the time that I submitted this tip to MSSQLTips.com, I or other immediate family members, held positions in a handful of stocks among those in the sheets. Last Updated: 2016-10-27. Post a comment or let the author know this tip helped. All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published.
Required fields are marked with an asterisk (.).Name.Email Email me updates. NOTE.
If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting. Signup for our newsletter I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the and understand I may unsubscribe at any time.
Thursday, September 13, 2018 - 9:12:33 PM - Rick Dobson I notice at least a couple of comments are interested in updating a database of stock prices in SQL Server. My initial tries at this were not aimed at initially setting up a database to get refreshes on some schedule. This is because my goal was to create a database that I research for analytical purposes. I am currently working through a series of analytical articles - some are for stock market data explicitly, and others are good for any kind of data. I was going to return eventually to doing more about data collection demonstrations. If I can get a series on another data collection series, you will not see it start until late 2018 or early 2019.
I the mean time feel free to send comments on specifically what you would like to see in a data collection series. Rick Dobson Thursday, September 13, 2018 - 4:40:41 PM - Alex Google is a decent way to get historical stock prices, but if anyone is curious about a more quality source with solid developer support, I'd recommend I've been using them since Yahoo Finance has gone down and they've been doing me well! Thursday, November 02, 2017 - 6:56:30 AM - Rick Dobson Ali, I would be glad to hear any questions that you have on the tip and the topic generally. This may lead to another tip. Can you consider connecting to me on LinkedIn. Then, we can start by exchanging messages on it.
Google 'Ricardo (Rick) Dobson on linkedin' to find my LinkedIn page and then just connect to me. Thursday, November 02, 2017 - 6:49:37 AM - Rick Dobson Sorry that it took so long to respond to comment about refreshing the data. I did not move on specifically to processes for updating the histories. However, I did author a pair of tips on automating sql jobs generally. Please take a look at two tips titled: ' and '.
Do you have specific suggestions about when updates should be performed? I am exporing downloading stock price histories to SQL Server via Python, which I view as a lot more productive approach. I will probably build out better solutions for it, including updating strategies. Wednesday, November 01, 2017 - 5:08:57 PM - Ali Zeidan Hello Rick, I have been looking for something like this for a while.
![How to historical price data from google finance api account How to historical price data from google finance api account](/uploads/1/2/5/4/125466141/483627961.png)
I would like to talk to you about few things that I have not been able to fiqure them out. Do you have an update post that I can use. Would you please let me know when will be a good time to talk. Regards, Ali Wednesday, December 21, 2016 - 3:35:25 PM - dmercc Great post, I have been searching for something like this.
But once imported. How do you update the database daily? Is there an automated process that fills in every quote, every day for every stock?