WinHttpRequest on Mac OSX Excel 2011 VBA

Question

There is no WinHttopRequest in "References" on Mac version of Excel 2011. I have tried following approaches which I have seen in other posts:

Set HTTP = CreateObject("MSXML2.ServerXMLHTTP")

which gives me a '429' runtime error: ActiveX controller can't create object.

Is there a way to use WinHttpRequest or something similar on Mac Excel? I have had no luck with query tables either, and want to avoid that solution. There should be a simple http GET solution to this problem I would think. Just cant find it out for Mac Excel.

I am trying to get data from Yahoo Finance api url:

Dim URL As String: URL = "http://finance.yahoo.com/d/quotes.csv?s=" & Symbols & "&f=snl1hg"
Dim HTTP As New WinHttpRequest
HTTP.Open "GET", URL, False
HTTP.Send

I know this works on windows, but I am using a Mac. Please advise. Thanks!

Did you find a solution for this? Facing a similar case. and I also want to avoid QueryTables — ronnyfm, Mar 21 '16 at 19:12

score 3 · Answer 1 · edited Mar 24 '17 at 20:37

You can use QueryTables in place of the HTTP Get call (WinHttopRequest), which evidently is not supported by Mac Excel 2011. The code below worked for me - enter the tickers in column, starting with A2, enter yahoo finance tags (i.e. a,b, r, n) in row starting with B1.

You can assemble the URL to call YF for the csv, then use a QueryTable to make the call and paste the results in your worksheet.

Code working on Mac Excel 2011:

Sub Yahoo_Finance_API_Call_MacExcel2011()
    Dim head As Range
    Set head = Range("A1")

    Dim wb As Workbook 'In the event that you'll use different workbooks
    Dim src As Worksheet 'In the event that you'll use different a source worksheet
    Dim tgt As Worksheet 'In the event that you'll use different a target worksheet

    Set wb = ThisWorkbook
    Set src = wb.Sheets("Sheet1")
    Set tgt = wb.Sheets("Sheet1")

    'Assemble Symbols for API Call
    Set rng = Range(head.Offset(1, 0), head.Offset(1, 0).End(xlDown))
    For Each cell In rng ' Starting from a cell below the head cell till the last filled cell
        Symbols = Symbols & cell.Value & "+"
    Next cell
    Symbols = Left(Symbols, Len(Symbols) - 1) ' Remove the last '+'

    'Assemble Tags or API Call
    Set rng = Range(head.Offset(0, 1), head.Offset(0, 1).End(xlToRight))
    For Each cell In rng ' Starting from a cell to the right of the head cell till the last filled cell
        tags = tags & cell.Value
    Next cell

    'Build URL
    URL = "TEXT;http://finance.yahoo.com/d/quotes.csv?s=" 'Use TEXT to collect API data below
    URL = URL & Symbols & "&f=" & tags

        'Range("A1").Value = URL 'This will output the assembled URL in a1 for QA if need be

    'Call API
    With tgt.QueryTables.Add(Connection:= _
            URL, _
            Destination:=Range(head.Offset(1, 1), head.Offset(1, 1).End(xlDown)))

        .RefreshStyle = xlOverwriteCells
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .BackgroundQuery = True
        .TextFileCommaDelimiter = True
        .TablesOnlyFromHTML = True
        .Refresh BackgroundQuery:=False
        .TextFilePromptOnRefresh = False
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .SaveData = False
    End With
End Sub

score 0 · Answer 2 · edited May 23 '17 at 12:32

0

I do not believe Macs have anything equivalent to MSXML.ServerXMLHTTP. A suggestion from another stackoverflow thread is to use QueryTables. In summary the thread suggests:

With ActiveSheet.QueryTables.Add(Connection:="URL;http://carbon.brighterplanet.com/flights.txt", Destination:=Range("A2"))
    .PostText = "origin_airport=MSN&destination_airport=ORD"
    .RefreshStyle = xlOverwriteCells
    .SaveData = True
    .Refresh
End With

edited May 23 '17 at 12:32

Community

1
1

answered Aug 16 '13 at 21:51

jeefreak

61
7

3

I'm looking for a more direct, http get solution. Are query tables really the only way to get data from yahoo finance in excel on a mac? That seems unlikely. – brno792 Aug 17 '13 at 02:31
@brno792 Were you able to find an optimal way to do this? – hax Mar 29 '17 at 19:48

WinHttpRequest on Mac OSX Excel 2011 VBA

2 Answers2

Linked