Get External Data from Web issue

[Deep Analysis]

Hi all,

I am trying to collect FInancial data from reuters.com, using Data - Get External data - From Web, where I simply paste, for example, this link AAPL.OQ - Apple Inc. Financials | Reuters and click Import.

However, when I get the data in Excel, I see everything except for the table headers (i.e. I don't see the years above the table) - cells are blank.

I would really appreciate your help to fix this issue.

Let me know if more info is needed. Attached is how I see the table in Excel. Thanks a lot!

 

[denzo36]

Hi there what version of Excel do you have?

 

[Deep Analysis]

Hi @denzo36 ,

Excel 2016 (forgot to mention).

 

[denzo36]

Ok you can use Power Query to connect to it

If you go the the Data tab -> New Query -> From Other Sources -> From Web then paste in your URL

From the resultant window, select the table on the left hand side (not document) - you'll then see a preview of the table

If you just want to take the table as it is then just click 'Load' to load it into the spreadsheet - if you want to transform the data in some way then select 'Edit' which will take you to the Power Query Editor

 

[Deep Analysis]

@denzo36 , thanks for the suggestion; however, it sems there is no way to loop this approach within the VBA macros.

Would there be any other alternatives using Data - Get External data - From Web?

 

[denzo36]

Are you trying to get all the data that is on the web page? I notice that there's Annual and Quarterly figures as well as Income Statement, Balance Sheet and Cash Flow that would manually need to click to get the figures to change - are you trying to 'loop' through these to get all the information on the web page?

 

[Deep Analysis]

Are you trying to get all the data that is on the web page? I notice that there's Annual and Quarterly figures as well as Income Statement, Balance Sheet and Cash Flow that would manually need to click to get the figures to change - are you trying to 'loop' through these to get all the information on the web page?

Even when I try to get data from web only for, lets say, Income statement annual - it doesn't show me the years on the top of the data. I.e. I get all the numbers, etc. However, I don't see for which years/periods they are. This is the main issue.

 

[sandy666]

there are dates as headers, do you see that?

Column1	26-Sep-20	28-Sep-19	29-Sep-18	30-Sep-17	24-Sep-16
Revenue	274515	260174	265595	229234	215639
Total Revenue	274515	260174	265595	229234	215639
Cost of Revenue, Total	169559	161782	163756	141048	131376
Gross Profit	104956	98392	101839	88186	84263
Selling/General/Admin. Expenses, Total	19916	18245	16705	15261	14194
Research & Development	18752	16217	14236	11581	10045
Total Operating Expense	208227	196244	194697	167890	155615
Operating Income	66288	63930	70898	61344	60024
Interest Inc.(Exp.),Net-Non-Op., Total	890	1385	2446	2878	2543
Other, Net	-87	422	-441	-133	-1195
Net Income Before Taxes	67091	65737	72903	64089	61372
Provision for Income Taxes	9680	10481	11872	15738	15685
Net Income After Taxes	57411	55256	61031	48351	45687
Net Income Before Extra. Items	57411	55256	61031	48351	45687
Net Income	57411	55256	59531	48351	45687
Income Available to Com Excl ExtraOrd	57411	55256	61031	48351	45687
Income Available to Com Incl ExtraOrd	57411	55256	59531	48351	45687
Diluted Net Income	57411	55256	59531	48351	45687
Diluted Weighted Average Shares	17528.21	18595.65	20000.44	21006.77	22001.12
Diluted EPS Excluding ExtraOrd Items	3.28	2.97	3.05	2.3	2.08
DPS - Common Stock Primary Issue	0.8	0.75	0.68	0.6	0.55
Diluted Normalized EPS	3.28	2.97	3.05	2.3	2.08
Total Extraordinary Items	-1500

 

[Deep Analysis]

Yes, did you get them via New Query from Web? In this case, I couldn't loop the VBA code. Different companies might have different dates, so using the same for all the companies is not an option.

 

[sandy666]

Power Query not tolerate vba

for different companies you can try query with parameter(s)