0

I'm having trouble getting response text and a response body returned when I run the code below. The "HTTP/1.1 200 OK" message comes back along with response headers, but no response body. I've confirmed this result using Fiddler2 and also looking a netsh trace log.

Other URLs (http://real-chart.finance.yahoo.com/table.csv?s=CELG&d=6&e=26&f=2014&g=d&a=2&b=26&c=1990&ignore=.csv) for example, do return response text as well as a response body.

Why is there a problem with this URL and how can I get it to return a response body?

Sub testlogin()

    fileUrl = "http://financials.morningstar.com/ajax/ReportProcess4CSV.html?t=XNYS:HFC&region=USA&culture=en-US&productCode=COM&reportType=is&period=&dataType=A&order=desc&columnYear=5&rounding=3&view=raw"

    Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1")

    WHTTP.Open "GET", fileUrl, False

    WHTTP.Send

    MsgBox WHTTP.Status
    MsgBox WHTTP.ResponseText
    MsgBox WHTTP.ResponseBody
    MsgBox WHTTP.GetAllResponseHeaders

    Set WHTTP = Nothing

End Sub
Cindy Meister
  • 25,071
  • 21
  • 34
  • 43
Osprey2k
  • 3
  • 1
  • 3

1 Answers1

0

Have you studied those response headers that are returned by the GET calls to both URLs?

Morningstar is like this:

Cache-Control: max-age=0
Connection: keep-alive
Date: Sat, 26 Jul 2014 22:07:33 GMT
Pragma: no-cache
Content-Length: 0
===>> Content-Type: text/html;charset=UTF-8 <<===
===>> Content-Encoding: gzip <<===
Server: Apache
Set-Cookie: JSESSIONID=6FAF41A612ABB32B0C670AB07BF0D8A5; HttpOnly
Vary: User-Agent
Vary: Accept-Encoding
com.coradiant.appvis: vid=ad&sid=CONTROLLER_1&tid=da615c36-2a18-4129-bcd7-1cbb139ab52b
Content-Disposition: attachment;filename=""HFC Income Statement.csv""
ExpiresDefault: access plus 2 hours

Yahoo Finance is like this:

Cache-Control: private
Connection: close
Date: Sat, 26 Jul 2014 22:10:00 GMT
Transfer-Encoding: chunked
===>> Content-Type: text/csv <<===
P3P: policyref=""http://info.yahoo.com/w3c/p3p.xml"", CP=""CAO DSP COR CUR ADM DEV TAI PSA PSD IVAi IVDi CONi TELo OTPi OUR DELi SAMi OTRi UNRi PUBi IND PHY ONL UNI PUR FIN COM NAV INT DEM CNT STA POL HEA PRE LOC GOV""
Set-Cookie: B=d3svnbl9t89po&b=3&s=4i; expires=Tue, 26-Jul-2016 22:10:00 GMT; path=/; domain=.yahoo.com
Vary: Accept-Encoding

I've sort-of highlighted the Content-Type and Content-Encoding headers (where available).

Basically, the content returned is different for the two calls. Clearly, Excel can interpret the second case where the content type is "text/csv" but the first one is a strange gzipped html page that I guess Excel can't understand.

I can't possibly give you a solution to this issue, but the content of the headers could certainly explain the difference in behaviour you're seeing.

djikay
  • 10,450
  • 8
  • 41
  • 52
  • thank you for your mention of looking at the headers. I tried playing with the request headers to see if I could get the Morningstar server to accommodate without much success. What's interesting is that I can see where WinHTTPRequest has default headers that it passes (I can see this in Fiddler2) that when sent by Fiddler directly return results. There seems to be something specific about WinHTTPRequest apart from the headers it sends that doesn't work with the Morningstar server. – Osprey2k Jul 27 '14 at 13:11
  • @Osprey2k: It could be that it just doesn't handle/understand gzipped content and just ignores it. I can't be sure, I haven't worked with it enough or in any great detail. – djikay Jul 27 '14 at 13:14
  • I think you are right... a few Google searches have turned up that WinHTTP and XMLHTTP don't handle the gzip compression. I think I will have to find another way to download CSV files from Morningstar. I know I can use URLDownloadToFile, I just need to learn how to use it with a password protected site. Thank you for your help. – Osprey2k Jul 27 '14 at 16:59
  • @Osprey2k: You are welcome. I hope you figure it out, good luck! – djikay Jul 27 '14 at 17:01