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:
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.
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.
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
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?
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
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?
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 VA, R&, SP$(), URL2$, V VA = Sheet1.Cells(1).CurrentRegion.Value If UBound(VA) < 7 Or UBound(VA, 2) < 3 Then Beep: Exit Sub Application.ScreenUpdating = False For Each V In [{"&reportType=","&period=","&dataType=A&order=","&columnYear=","&number="}] R = R + 1 URL2 = URL2 & V & VA(R, 3) Next With CreateObject("Msxml2.XMLHTTP") For R = 7 To UBound(VA) If Evaluate("ISREF('" & VA(R, 1) & "'!A1)") Then Worksheets(VA(R, 1)).UsedRange.Clear _ Else Worksheets.Add(, Worksheets(Worksheets.Count)).Name = VA(R, 1) .Open "GET", URL1 & VA(R, 1) & URL2, False .setRequestHeader "DNT", "1" On Error Resume Next .send On Error GoTo 0 If .Status = 200 Then SP = Split(.responseText, vbLf): SP(0) = Replace$(SP(0), "???", "") With Worksheets(VA(R, 1)).Cells(1).Resize(UBound(SP) + 1) .Value = Application.Transpose(SP) .TextToColumns Comma:=True, DecimalSeparator:="." .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 …
Re: url api append csv downloads to active workbook
Originally Posted by AB33
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.
[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:
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?
LinkBack URL
About LinkBacks
Register To Reply
Re: url api append csv downloads to active workbook
♪ Better, Faster, Stronger ! ♫
Originally Posted by Nerpilis
Bookmarks