+ Reply to Thread
Results 1 to 19 of 19

url api append csv downloads to active workbook

  1. #1
    Registered User
    Join Date
    09-22-2011
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    38

    url api append csv downloads to active workbook

    I've been stuck on this for a while. Probably because I'm a VBA newbie. I'm basing this mostly off some code i think was originated by Chip Pearson which I've been using for years, but I can now only find the source of my inspiration this forum post:

    http://stackoverflow.com/questions/9...net-into-excel

    While that post focuses on retrieving Yahoo finance historical price data through a url structure, I want to apply similar actions to a different url structure.

    Essentially I have found an api/url structure for MorningStar that downloads various types of financials. I would like to begin a similar data scrape from a list of tickers given certain parameters and append each csv download into the active workbook. I'd even be ok if i could just get the csv's to download into a local directory. Below is my code thus far, it seems to create the 'tabs' for the tickers but the data is not placed there. I have also uploaded the file I'm using so you can see where the parameters are located and the url structures in the 'reference' tab.

    Please Login or Register  to view this content.
    would love any advice even if it upends my current logic...any learning links would also help me too!
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: url api append csv downloads to active workbook

    Your code has errors.

    What are the values for

    ReportType = ParameterRng.Offset(0, 1).Value
    Period = ParameterRng.Offset(0, 2).Value
    Order = ParameterRng.Offset(0, 3).Value
    YearsReported = ParameterRng.Offset(0, 4).Value
    Unit = ParameterRng.Offset(0, 5).Value

    Do you mean
    Ticker = Cell.Value
    ReportType = Sh.Range("B1")
    Period = Sh.Range("B2")
    Order = Sh.Range("B3")
    YearsReported = Sh.Range("B4")
    Unit = Sh.Range("B5")
    ?
    If so, the code returns nothing. So, we first need to fix we are downloading the right csv file which also means to navigate the right page. The second issue is simple: save the files in a directory.

  3. #3
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    8,418

    Lightbulb Re: url api append csv downloads to active workbook



    Even do not need to download any file but just directly read data via a web request

  4. #4
    Registered User
    Join Date
    09-22-2011
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    38

    Re: url api append csv downloads to active workbook

    Thanks you!

    yes the Ticker list begins in cell A7 and goes down depending on the # of tickers.
    The and other variable values you questioned are correct in terms of row reference except they should be referencing column C

  5. #5
    Registered User
    Join Date
    09-22-2011
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    38

    Re: url api append csv downloads to active workbook

    Ideally this would be better than saving to a directory... but i guess i'd like to learn how to do that to.

  6. #6
    Registered User
    Join Date
    09-22-2011
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    38

    Re: url api append csv downloads to active workbook

    Thanks you for pointing out certain 'bad references'...... is this better?

    Please Login or Register  to view this content.

  7. #7
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: url api append csv downloads to active workbook

    Yes, it is. You can use a number of methods. I have used a different method. I will try to adjust your code new code
    Attached Files Attached Files
    Last edited by AB33; 12-14-2016 at 04:14 PM.

  8. #8
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: url api append csv downloads to active workbook

    Sorry!
    There is an error on name of the file, please use this line

    .SaveToFile "C:\Users\AB\Desktop\" & FileName & ".csv", 2 ' change to suite your needs

    But you still name the name of the folder. The file is save with the name of the ticker on my desktop

  9. #9
    Registered User
    Join Date
    09-22-2011
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    38

    Re: url api append csv downloads to active workbook

    Ahh Thank you!!.... I was already experimenting with that and figured it out on my own with trial/error.....likely i will adjust and/or create different folders depending on the report type.... I will have to work on subsequent reading/parsing the files .... another thread another day.

    Thank you again!

    Before I mark this "solved", user Mark L had mentioned something about just reading the data in..... how would i go about adjusting to accomplish this or write the csv data into separate tabs within this single excel file?

  10. #10
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: url api append csv downloads to active workbook

    Do you mean all the tickers with their own name on the same workbook?

  11. #11
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: url api append csv downloads to active workbook

    Both codes attached. The CSV code will be slower since file is downloaded as CSV, not separated by comma.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    09-22-2011
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    38

    Re: url api append csv downloads to active workbook

    That works beautifully....

  13. #13
    Registered User
    Join Date
    09-22-2011
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    38

    Re: url api append csv downloads to active workbook

    Actually if your still around I had one more quick question:

    the Sub DelimitData () looks for the "," to separate and rewrite the data ..... some of the fields where there are txt descriptions use comma's within their descriptions which kind of shifts the data in that row over such that data points don't line up under the correct column heading. Visually it's easy to recognize and it seems to do it for the same fields. I suppose i could write more involved calculation formulas to identify these fields and shift the data points, but my plans for this spreadsheet will get much more complex if i have to test for these anomalies..... the csv download files don't do this, but I prefer the tab delimited .... is there a way to identify these unusual fields so that the data tables look more like the tables in the csv files?

    thanks again.... either way I'm well on my way to the master plan

  14. #14
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: url api append csv downloads to active workbook

    Well, It is CSV-comma separated values. That is what you get (Text-to columns) unless you are going to go though loops. Have you noticed that the saved files with the first code gives you a different outcome?

  15. #15
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    8,418

    Cool ♪ Better, Faster, Stronger ! ♫

    Quote Originally Posted by Nerpilis View Post
    Mark L had mentioned something about just reading the data in
    As I wrote, you can directly read data via a request, the same used by webbrowsers
    as you can see with your webbrowser inner inspector tool !

    It's like QueryTable object but in a faster way :

    PHP Code: 
    Sub Demo()
      Const 
    URL1 "http://financials.morningstar.com/ajax/ReportProcess4CSV.html?t="
        
    Dim VAR&, SP$(), URL2$, V
            VA 
    Sheet1.Cells(1).CurrentRegion.Value
            
    If UBound(VA) < Or UBound(VA2) < 3 Then Beep: Exit Sub
            Application
    .ScreenUpdating False
        
    For Each V In [{"&reportType=","&period=","&dataType=A&order=","&columnYear=","&number="}]
               
    1
            URL2 
    URL2 VA(R3)
        
    Next
        With CreateObject
    ("Msxml2.XMLHTTP")
            For 
    7 To UBound(VA)
                If 
    Evaluate("ISREF('" VA(R1) & "'!A1)"Then Worksheets(VA(R1)).UsedRange.Clear _
                                
    Else Worksheets.Add(, Worksheets(Worksheets.Count)).Name VA(R1)
                .
    Open "GET"URL1 VA(R1) & URL2False
                
    .setRequestHeader "DNT""1"
                    
    On Error Resume Next
                
    .send
                    On Error 
    GoTo 0
                
    If .Status 200 Then
                        SP 
    Split(.responseTextvbLf):  SP(0) = Replace$(SP(0), "???""")
                    
    With Worksheets(VA(R1)).Cells(1).Resize(UBound(SP) + 1)
                        .
    Value Application.Transpose(SP)
                        .
    TextToColumns Comma:=TrueDecimalSeparator:="."
                        
    .Columns("A:B").AutoFit
                    End With
                End 
    If
            
    Next
        End With
            Application
    .ScreenUpdating True
    End Sub 
    Do you like it ? So thanks to click on bottom left star icon Add Reputation !
    Last edited by Marc L; 12-14-2016 at 10:56 PM. Reason: optimization

  16. #16
    Registered User
    Join Date
    09-22-2011
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    38

    Re: url api append csv downloads to active workbook

    Quote Originally Posted by AB33 View Post
    Well, It is CSV-comma separated values. That is what you get (Text-to columns) unless you are going to go though loops. Have you noticed that the saved files with the first code gives you a different outcome?
    yes, I have noticed differences, however so slight. I understand that it's looking for the comma then using that as a trigger to create a new column for that row. for the example tickers i gave the "Sales, General and administrative" row had shifted and is present among all the examples. There was ticker "OMI" had a one off additional trigger too ....however as i comb through the universe of tickers there could be different entry 'rows' with different syntax and i expect this to be common enough to carefully consider my options....I will have to perform a population sample etc to get all the different varieties and how often they appear among the different report types and ticker types. Eventually I will want to transpose all this data into the ticker line once I figure out all the fields and their various syntax..... such that i can use power pivots and other calculation and statistical methods.

    Potential solution; currently I'm thinking if I download all the csv files (while less efficient) perhaps i can read the columns in each csv download file and transpose back into the original spreadsheet since the download files don't have the shifted columns and I can search for text strings to bring in the values I want.

  17. #17
    Registered User
    Join Date
    09-22-2011
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    38

    Re: ♪ Better, Faster, Stronger ! ♫

    Quote Originally Posted by Marc L View Post
    [INDENT]As I wrote, you can directly read data via a request, the same used by webbrowsers
    as you can see with your webbrowser inner inspector tool !

    It's like QueryTable object but in a faster way :
    sorry about the misspelling

    I'm intrigued yet unfamiliar with many aspects of this code. I tried to append it to one of the modules from AB33's corrected sheet.... however when i ran it I got 'runtime error code 9 , script out of range' in this line:
    Please Login or Register  to view this content.
    is that because it's php code?

    While I have used inspector tools to gather image sizing/location info, (I also believe that was used in figuring out the url structure) I'm unfamiliar with php or html. I do like the on error resume next.... will need to incorporate something similar in AB33's edits. also a clever way to have url1 and url2 and other variables used in the loop. I just can't seem to get it to work, am i missing something?

  18. #18
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    8,418

    Re: url api append csv downloads to active workbook


    No, I use PHP code tags but it's just a VBA code, working like a breeze on my side with your sample attachment !

    Check variables in Locals window to find out the source of this issue …

  19. #19
    Registered User
    Join Date
    09-22-2011
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    38

    Re: url api append csv downloads to active workbook

    ahhh I had to go back to the pre edited version before AB33's input.... it works like a charm on the earlier version. lots for me to learn from

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Saving as a PDF on the active workbook Path and name same as active workbook name
    By subbby in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-22-2016, 12:25 PM
  2. Open Another Workbook from Active Workbook but stay on Active Workbook.
    By Tona in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-29-2014, 06:33 AM
  3. [SOLVED] Code to detect previous active workbook instead of current active workbook
    By kosherboy in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-25-2014, 01:58 AM
  4. VBA macro for hyperlink to active workbook in the active workbook path
    By Scott Taylor in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-10-2013, 05:37 AM
  5. Macro to Print active sheet as PDF to Active workbook and customize name
    By Scott Taylor in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-17-2012, 08:52 PM
  6. how can worksheet_activate event of active sheet work of active workbook
    By tahir mahmood in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-20-2011, 08:19 AM
  7. Append Data From One Workbook to Another Workbook (Consolidate Random Columns)
    By capnhud in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 06-23-2010, 01:15 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1