用EXCEL爬取网页获得基金重仓股票数据

前面文章用Python和Powershell爬取网页，获得基金数据，再导入到EXCEL进行分析。

后来发现使用PowerQuery for EXCEL可以直接抓取网页数据，功能很强大。

M代码

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    更改的类型 = Table.TransformColumnTypes(源,{{"基金代码", type text}, {"基金名称", type text}}),
    已添加自定义 = Table.AddColumn(更改的类型, "自定义", each Json.Document(Web.Contents("https://danjuanapp.com/djapi/fund/detail/"&[基金代码]))),
    #"展开的“自定义”" = Table.ExpandRecordColumn(已添加自定义, "自定义", {"data"}, {"data"}),
    #"展开的“data”" = Table.ExpandRecordColumn(#"展开的“自定义”", "data", {"fund_position"}, {"fund_position"}),
    #"展开的“fund_position”" = Table.ExpandRecordColumn(#"展开的“data”", "fund_position", {"stock_list"}, {"stock_list"}),
    #"展开的“stock_list”" = Table.ExpandListColumn(#"展开的“fund_position”", "stock_list"),
    #"展开的“stock_list”1" = Table.ExpandRecordColumn(#"展开的“stock_list”", "stock_list", {"name", "code", "percent", "current_price"}, {"name", "code", "percent", "current_price"}),
    更改的类型1 = Table.TransformColumnTypes(#"展开的“stock_list”1",{{"name", type text}, {"code", type text}, {"percent", type number}, {"current_price", type number}}),
    被除的列 = Table.TransformColumns(更改的类型1, {{"percent", each _ / 100, type number}}),
    重命名的列 = Table.RenameColumns(被除的列,{{"name", "重仓股票"}, {"code", "股票代码"}, {"percent", "持仓比例"}, {"current_price", "股票现值"}})
in
    重命名的列