none
Converting a YAHOO/GOOGLE Historical Stock data Excel Macro to accept BarCode.Com API RRS feed

  • Question

  • Due to some health issues, I am finally looking into redoing my GOOGLE historical data extract (Originally YAHOO) and input the data automatically into my Excel worksheet. I have no clue as to how to accomplish the code changes to go from the GOOGLE API to barcharts.com API and automatically download the historical data into my Excel worksheet "BackTest". I now have a free barcharts.com data account, that allows for 2 years of historical data per download. I need to convert the Excel Macro below to get the data from barcharts.com API into the first 9 columns of the "BackTest" worksheet.
    GOAL:
    - Have my old Excel GOOGLE Macro connect to barchart.com
    - Download the historical data into my Excel worksheet "BackTest" 1st 9 columns
    I know I do not need the timestamp field, but to eliminate any extra pain, I can ignore it.
    Any assistance will greatly be appreciated.

    Don

    Sorry if the format did not come out correctly....

    barchart.com input:
    https://marketdata.websol.barchart.com/getHistory.csv?
    apikey=<MYKEY>&symbol=AAPL&type=daily&startDate=20100101&endDate=20130101&maxRecords=10&interval=60&order=asc&sessionFilter=EFK&splits=true&dividends=true&volume=sum&nearby=1&jerq=true&exchange=NYSE%2CAMEX%2CNASDAQ&backAdjust=false&daysToExpiration=1&contractRoll=expiration
    
    Output: displayed on browser page
    symbol,timestamp,tradingDay,open,high,low,close,volume,openInterest
    "MET","2018-04-02T00:00:00-04:00","2018-04-02","45.85","46.05","44.44","45.02","7454700",""
    "MET","2018-04-03T00:00:00-04:00","2018-04-03","45.26","45.83","44.96","45.63","6481400",""
    "MET","2018-04-04T00:00:00-04:00","2018-04-04","44.68","45.94","44.58","45.85","8082100",""
    "MET","2018-04-05T00:00:00-04:00","2018-04-05","46.15","46.37","45.82","46.03","5740700",""
    "MET","2018-04-06T00:00:00-04:00","2018-04-06","45.47","45.96","44.84","45.27","8637000",""
    "MET","2018-04-09T00:00:00-04:00","2018-04-09","45.59","46.415","45.51","45.55","4904000",""
    "MET","2018-04-10T00:00:00-04:00","2018-04-10","46.25","46.81","46.12","46.46","4742000",""
    "MET","2018-04-11T00:00:00-04:00","2018-04-11","46.05","46.49","45.85","46.05","4781700",""
    "MET","2018-04-12T00:00:00-04:00","2018-04-12","46.53","47.32","46.43","47.03","5150800",""
    "MET","2018-04-13T00:00:00-04:00","2018-04-13","47.39","47.49","46.695","46.92","4520800",""
    

    Excel Macro (used for YAHOO historical data)

    Sub DataDownload()

    Dim QuerySheet As Worksheet Dim SettingsSheet As Worksheet Dim EndDate As Date Dim startdate As Date Dim SYMBOL As String Dim qurl As String Dim LastRow1 As Long Dim LastRow2 As Long Dim LastRow3 As Long Application.ScreenUpdating = False Application.DisplayAlerts = False 'Application.Calculation = xlCalculationManual ' Clear all cells in columns A:G formula rows from H5 through AF last row Worksheets("BackTest").Range("a:g").ClearContents ' Get Yahoo Stock History Data Set SettingsSheet = Worksheets("Setup") SYMBOL = SettingsSheet.Range("E4") startdate = SettingsSheet.Range("E6").Value EndDate = SettingsSheet.Range("E7").Value qurl = "http://finance.google.com/finance/historical?q=" & SYMBOL qurl = qurl & "&startdate=" & MonthName(Month(startdate), True) & _ "+" & Day(startdate) & "+" & Year(startdate) & _ "&enddate=" & MonthName(Month(EndDate), True) & _ "+" & Day(EndDate) & "+" & Year(EndDate) & "&output=csv" 'QueryQuote: Set QuerySheet = Worksheets("BackTest") With QuerySheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=QuerySheet.Range("a2")) .BackgroundQuery = True .TablesOnlyFromHTML = False .Refresh BackgroundQuery:=False .SaveData = True End With On Error GoTo StockSymbolEntryError With Worksheets("BackTest") LastRow1 = .Range("H" & .Rows.Count).End(xlUp).Row .Range("H6:af" & LastRow1).Clear Sheets("BackTest").Range("a1").CurrentRegion.TextToColumns Destination:=Sheets("BackTest").Range("a1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=True, Space:=False, other:=False End With Sheets("BackTest").Columns("A:ah").AutoFit With Worksheets("BackTest") LastRow2 = .Range("A" & .Rows.Count).End(xlUp).Row End With With Worksheets("BackTest") .Range("h5:af5").AutoFill Destination:=.Range("h5:af" & LastRow2 - 5) End With With Worksheets("SetUp") Application.CutCopyMode = False Range("E4").Select Selection.Copy Range("i5").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveSheet.Paste Application.CutCopyMode = False End With StockSymbolEntryError: 'MsgBox "Symbol (" & SYMBOL & ") not found.", vbCritical + vbOKOnly, "Symbol Not Found" 'MsgBox Err.Number & " " & Err.Description 'CopyCurrentDataIntoLine2 'CopyLine2HeaderToLine1 Sheets("BackTest").Select Call CopyLine2HeaderToLine1 Call CorrectHeaderDateField Call GetCurrentStockData Call DeleteQuotesFromDateField ' Causing error since no yahoo data available 'Call ConvertTextInCellToNumber Call FixDateRow Sheets("Setup").Select Range("E4").Select MsgBox "Calculations Completed" 'Call CopyToDisplay() 'MsgBox "Copy Data to SummaryReport Complete" End Sub



    Sunday, April 15, 2018 11:43 PM

Answers

  • I apologize, I believe that this would probably be best answered in the VBA forum. I agree it should be moved to that forum

    Thanks

    Don

    Don,

    My apologies. If I knew how to read VBA I would be of more help. I keep feeling that this would be a breeze to do in Power Query.

    If you have Excel 2016 or you have Power Query for Excel 2010 or 2013 installed, then I'd highly encourage you to read the following article.

    Monday, April 16, 2018 3:57 PM

All replies

  • Hey Mustang!

    Are you trying to do this with VBA or with Power Query? every API is different, so perhaps the old API that you were getting the data from is not the same as the one from barcharts.com

    Monday, April 16, 2018 10:54 AM
  • Hey Mustang!

    Are you trying to do this with VBA or with Power Query? every API is different, so perhaps the old API that you were getting the data from is not the same as the one from barcharts.com


    I guess I am confused, as the BarChart API that I am using (API for CVS output format) works fine at getting the data downloaded into my browser page as shown in sample output. I just do not know how to replace the GOOGLE/YAHOO code in the Excel macro and replace it with the BarChart API code (included) used to download the history data, and then get that data into the 1st 9 columns of the BackTest worksheet. Granted the YAHOO/GOOGLE download only had 7 fields and the Barchart download has 9:

    Yahoo extract fields:
    Date Open High Low Close Volume Adj Close

    BarChart extract has 9 fields:
    symbol,timestamp,tradingDay,open,high,low,close,volume,openInterest

    Granted, the YAHOO extract has 7 fields of data, where as BarCharts extract has 9 fields of data. I have adjusted my worksheet to accept the additional fields as it is easier to ignore the additional data than try to only extract the needed fields. Again that is well beyond my capabilities, but I may eventually attempt to try eliminating the extra data.

    The current Excel Macro (which worked GREAT):
    1- connect to YAHOO
    2- pass the needed download request
    3- Verify valid stock quote name
    4- download the requested data
    5- read the downloaded data and enter that data into the first 6 fields of the BackTest worksheet

    The fields that are used by my spreadsheet are: Date Open High Low Close Volume

    Thanks
    Don
    Monday, April 16, 2018 12:47 PM
  • Hey Mustang,

    I'm still not sure if you're trying to use VBA for your solution or if you're trying to use Power Query. Have you tried using Power Query? this seems quite straight forward in Power Query. I don't have an API Key to test it out, so I'm helpless in that regard, but since you already have the url:

    https://marketdata.websol.barchart.com/getHistory.csv?
    apikey=<MYKEY>&symbol=AAPL&type=daily&startDate=20100101&endDate=20130101&maxRecords=10&interval=60&order=asc&sessionFilter=EFK&splits=true&dividends=true&volume=sum&nearby=1&jerq=true&exchange=NYSE%2CAMEX%2CNASDAQ&backAdjust=false&daysToExpiration=1&contractRoll=expiration
    

    then you should be able to see the results in Power Query with no issues.

    If you NEED to have your solution in VBA, then I'd request the help of a Moderator so we can move this thread to the appropriate forum (probably the Excel IT Pros forum¿?)

    Monday, April 16, 2018 1:14 PM
  • I apologize, I believe that this would probably be best answered in the VBA forum. I agree it should be moved to that forum

    Thanks

    Don

    Monday, April 16, 2018 2:10 PM
  • I apologize, I believe that this would probably be best answered in the VBA forum. I agree it should be moved to that forum

    Thanks

    Don

    Don,

    My apologies. If I knew how to read VBA I would be of more help. I keep feeling that this would be a breeze to do in Power Query.

    If you have Excel 2016 or you have Power Query for Excel 2010 or 2013 installed, then I'd highly encourage you to read the following article.

    Monday, April 16, 2018 3:57 PM
  • If you have Excel 2016 or you have Power Query for Excel 2010 or 2013 installed, then I'd highly encourage you to read the following article.

    Miguel,

    Thank you... I read the article on Power Query, tried it and the Barchart API worked and I believe that it will work fine. Learn something new every day. Now I ned to try and find out how to start the query from a Excel macro and force the data into the 1st 8 columns of my BackTest work sheet.

    Thanks again

    Don

    Monday, April 16, 2018 5:53 PM
  • These resources might help you with your VBA:

    https://social.technet.microsoft.com/Forums/en-US/d4e3816f-e2a6-49f5-83eb-30bf8c65ef48/is-it-possible-to-order-refresh-connections-within-power-query-?forum=powerquery

    https://blog.crossjoin.co.uk/2015/06/10/power-queryexcel-2016-vba-examples/


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Sunday, April 29, 2018 5:55 AM
    Moderator
  • Hi Mustan1965,

    did any of the provided solutions solve your problem? 

    Then please mark it as answer, as it will help others to find a solution to a similar problem. Thx!


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Wednesday, June 27, 2018 8:55 PM
    Moderator
  • One super easy way to get historical stock data into Excel is through https://intrinio.com/financial-data/us-fundamentals-financials-metrics-ratios-stock-prices?method=download. Makes it super easy to get the data you need in the right format and you can get it through a free trial.
    Thursday, September 13, 2018 4:40 PM