Data tables

[tenspeed2]

I have the 2019 Excel. After entering the url I am seeing 25 data tables. Question is I only need two tables.
As of now I have to open most of the tables to find the two I need. Is there a way to locate the two tables
I want to download into Excel?

 

[hernantorres23]

Hi, is only a basic way, Python would be a good choice
You will need check this example, for me there are something to do (replace currency symbol, convert text to number,etc.)
I hope, I helped you

Copy and paste the code...Menu Data\New Query\From Other Sources\Blank Query

//StatementOfOperations
let
    Source = Web.Page(Web.Contents("https://www.sec.gov/Archives/edgar/data/1176199/000114544319000436/d364003.htm")),
    #"ReadAllData" = Table.ExpandTableColumn(Source, "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Kind", "Name", "Children", "Text"}, {"Data.Column1", "Data.Column2", "Data.Column3", "Data.Column4", "Data.Column5", "Data.Column6", "Data.Column7", "Data.Column8", "Data.Column9", "Data.Column10", "Data.Column11", "Data.Kind", "Data.Name", "Data.Children", "Data.Text"}),
    #"RowsFilter" = Table.SelectRows(#"ReadAllData", each ([Data.Column1] = "Accounting and legal services fees" or [Data.Column1] = "Change in net unrealized appreciation (depreciation) of" or [Data.Column1] = "Custodian fees" or [Data.Column1] = "Dividends" or [Data.Column1] = "Expenses" or [Data.Column1] = "Futures contracts" or [Data.Column1] = "Increase in net assets from operations" or [Data.Column1] = "Interest" or [Data.Column1] = "Interest expense" or [Data.Column1] = "Investment income" or [Data.Column1] = "Investment management fees" or [Data.Column1] = "Less expense reductions" or [Data.Column1] = "Less foreign taxes withheld" or [Data.Column1] = "Net expenses" or [Data.Column1] = "Net investment income" or [Data.Column1] = "Net realized and unrealized gain" or [Data.Column1] = "Net realized gain (loss) on" or [Data.Column1] = "Other" or [Data.Column1] = "Printing and postage" or [Data.Column1] = "Professional fees" or [Data.Column1] = "Realized and unrealized gain (loss)" or [Data.Column1] = "Stock exchange listing fees" or [Data.Column1] = "Swap contracts" or [Data.Column1] = "Total expenses" or [Data.Column1] = "Total investment income" or [Data.Column1] = "Transfer agent fees" or [Data.Column1] = "Trustees' fees" or [Data.Column1] = "Unaffiliated investments" or [Data.Column1] = "Unaffiliated investments and foreign currency transactions") and ([Data.Column4] = null)),
    #"DropColumns" = Table.RemoveColumns(#"RowsFilter",{"Caption", "Source", "ClassName", "Id", "Data.Column3", "Data.Column4", "Data.Column5", "Data.Column6", "Data.Column7", "Data.Column8", "Data.Column9", "Data.Column10", "Data.Column11", "Data.Kind", "Data.Name", "Data.Children", "Data.Text"}),
    #"ChangeColNames" = Table.RenameColumns(#"DropColumns",{{"Data.Column1", "Col1"}, {"Data.Column2", "Col2"}})
in
    #"ChangeColNames"

 

[sandy666]

maybe with table of defined words are you looking for

Lookup		Custom	Column2
contract		10-year u.s. treasury note futures	640
divide		unaffiliated investments, at value (cost $825,732,402)	$846,771,907
expense		collateral held at broker for futures contracts	747000
fee		dividends and interest receivable	3346485
future		payable for futures variation margin	49987
increase		accounting and legal services fees	68271
Interest		trustees' fees	526
Investment		other liabilities and accrued expenses	127800
tax		dividends	$42,737,431
unaffiliate		less foreign taxes withheld	-60790
unrealize		investment management fees	6221176
		interest expense	9100628
		accounting and legal services fees	116543
		transfer agent fees	27548
		trustees' fees	44674
		custodian fees	65430
		professional fees	63383
		stock exchange listing fees	25387
		total expenses	15866124
		less expense reductions	-63052
		net expenses	15803072
		unaffiliated investments and foreign currency transactions	-1615670
		futures contracts	-3626652
		swap contracts	331029
		unaffiliated investments	18673369
		futures contracts	-1261066
		swap contracts	-3149654
		net realized and unrealized gain	9351356
		increase in net assets from operations	$44,194,047
		change in net unrealized appreciation (depreciation)	14262649
		increase in net assets resulting from operations	44194047
		from tax return of capital	-7360791
		issued pursuant to dividend reinvestment plan	1068633
		total increase (decrease)	1515488
		issued pursuant to dividend reinvestment plan	50306
		net increase in net assets from operations	$44,194,047
		collateral held at broker for futures contracts	-74984
		dividends and interest receivable	-1250363
		payable for futures variation margin	19984
		other liabilities and accrued expenses	-56142
		net increase in cash	$57,611
		net realized and unrealized gain (loss) on investments	0.35
		from tax return of capital	-0.28
		expenses before reductions	2.95
		expenses including reductions	2.94
		futures	$(1,416,381)
		swap contracts	-1084408

// https://www sec gov/Archives/edgar/data/1176199/000114544319000436/d364003 htm
let
    Source = Web.Page(Web.Contents("https://www.sec.gov/Archives/edgar/data/1176199/000114544319000436/d364003.htm")),
    Expand = Table.ExpandTableColumn(Source, "Data", {"Column1", "Column2", "Column3"}, {"Column1", "Column2", "Column3"}),
    Criteria = Table.TransformColumns(Table.AddColumn(Table.SelectColumns(Expand,{"Column1", "Column2"}), "Custom", each Table1),{{"Column1", Text.Lower, type text}}),
    Condition = Table.AddColumn(Table.ExpandTableColumn(Criteria, "Custom", {"Lookup"}, {"Lookup"}), "Custom", each if Text.Contains([Column1], [Lookup]) then [Column1] else null),
    Filter = Table.Distinct(Table.SelectColumns(Table.SelectRows(Condition, each ([Custom] <> null) and ([Column2] <> "Number of#(cr)#(lf)contracts" and [Column1] <> null and [Column1] <> "" and [Column1] <> "*" and [Column1] <> "* * *") and ([Column2] <> "")),{"Custom", "Column2"}))
in
    Filter

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Lower = Table.TransformColumns(Source,{{"Lookup", Text.Lower, type text}})
in
    Lower

 

[tenspeed2]

Sandy Thanks for the help. Good reply.
Max

 

[hernantorres23]

maybe with table of defined words are you looking for

This is an excelente choice, but when you work with Finance, there is an structure that you must follow, in this case, you may receive data that does not correspond to the financial statement (Statement of Operations), you just have to be careful with this.

 

[sandy666]

The Table of defined words may contain a few words in a cells not single word only, like:
Less expense reductions
foreign taxes
statement of operations
and so on...

On the end, you can filter result manually if necessary

 

[hernantorres23]

The Table of defined words may contain a few words in a cells not single word only, like:
Less expense reductions
foreign taxes
statement of operations
and so on...

On the end, you can filter result manually if necessary

Exactly, based in you first answer (proposed), it necessary this clarify, because the users could be use an single word, waiting right results

 

[sandy666]

I assumed people are thinking (sometimes) [joke]