-1

I want to build a worksheet where the user can input a Stock symbol and the balance sheet is automatically downloaded from morningstar to a table, and then some custom formulas are run on the data.

On github I found the hidden morningstar API: https://gist.github.com/hahnicity/45323026693cdde6a116

E.g. to retrieve some financials, I use (in my browser): http://financials.morningstar.com/ajax/ReportProcess4CSV.html?t=TWTR&reportType=is&period=12&dataType=A&order=asc&columnYear=5&number=3

Now I tried to start by building a query using Data->New Query->From different source->From web, I entered the URL and instead of seeing the actual csv-data as in my browser, a popup appears to actually open the file in a new Excel window. Even when trying to query a csv and entering the URL there, it doesnt work.

The popup looks like this: enter image description here

I do NOT want to open this file, I want to query the csv data!

Any ideas why it doesnt work? And how to make it actually work?

Thanks a lot

  • Why downvote? Not a valid question? – tim Jan 15 at 18:25
1

I do not know of a way for Excel to do what you wish natively; however, I have written a procedure that I used to use for downloading stock data. In my case I used to use yahoo.finance.com but you can modify this pretty easily for the site you are getting your stock data from:

Sub GetData()

Dim QuerySheet As Worksheet
Dim DataSheet As Worksheet
Dim qurl As String
Dim i As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual

Set DataSheet = ActiveSheet

range("C7").CurrentRegion.ClearContents
i = 7
qurl = "http://ichart.finance.yahoo.com/table.csv?s=" + Cells(i, 1)
i = i + 1
While Cells(i, 1) <> ""
    qurl = qurl + "+" + Cells(i, 1)
    i = i + 1
Wend
qurl = qurl + "&f=" + "nl1vhgd1rm4"

QueryQuote:
         With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.range("C7"))
            .BackgroundQuery = True
            .TablesOnlyFromHTML = False
            .Refresh BackgroundQuery:=False
            .SaveData = True
        End With

        range("C7").CurrentRegion.TextToColumns Destination:=range("C7"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
            Semicolon:=False, Comma:=True, Space:=False, other:=False


'turn calculation back on
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True

Columns("C:C").ColumnWidth = 25.43
range("h2").Select

End Sub
  • Thanks for the code, I'll give it a shot. But why shouldnt it be possible? From what I understood is: Excel should be able to connect with Data which comes from the web (http:// address) isnt it? So why not a csv from a web address? – tim Jan 15 at 17:36
  • @tim I never said it wasn't possible just that I don't know how to. Perhaps a way exists out there. Plus I personally like code that I can customize to perform a task exactly how I want it done. – Eric F Jan 15 at 19:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.