1 let
 2     timestamp=Text.From(Int64.From(Duration.TotalSeconds(DateTime.LocalNow()-#datetime(1970,1,1,8,0,0)))),
 3     kbzw__Session=Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="cookie"]}[Content],{{"kbzw__Session#(tab)", type text}}){0}[#"kbzw__Session#(tab)"],
 4     url="https://www.jisilu.cn/data/cbnew/cb_list_new/?___jsl=LST___t=1677990096350",//必填項:網(wǎng)頁真實的網(wǎng)址鏈接
 5     headers=[#"Content-Type"="application/json; charset=utf-8",Cookie="kbz_newcookie=1; kbzw__user_login=7****_P1ebax9aX3svk0O7Y4d-VooKvpuXK7N_u0ejF1dSeqcXWktmv26GvzaiSpper2KPclaHD1dyumd6fqpnamtyYrqXW2cXS1qCaq56olaiVmLKgzaLOvp_G5OPi2OPDpZalp5OguNnP2Ojs3Jm6y4KnkaWnrpi42c-qrbCJ8aKri5ai5-ff3bjVw7_i6Ziun66QqZeXn77Atb2toJnh0uTRl6nbxOLmnJik2NPj5tqYqp2nkKaPp6WjmLTRx9Xr3piun66QqZc.; kbzw__Session="&kbzw__Session&"; Hm_lvt_164fe01b1433a19b507595a43bf58262="&timestamp&"; Hm_lpvt_164fe01b1433a19b507595a43bf58262="&timestamp&"",Referer="https://www.jisilu.cn/data/cbnew/"], //必填項:Content-Type和登錄的cookie是必填項，Referer防盜鏈接最好填寫上
 6     query=[], //選填項:網(wǎng)址的一些查詢參數(shù)，一般Requset_ URL中?后面的部分，既可以寫在URl里面，也可以寫在這里面
 7     content="fprice=&tprice=&curr_iss_amt=&volume=&svolume=&premium_rt=&ytm_rt=&rating_cd=&is_search=N&market_cd%5B%5D=shmb&market_cd%5B%5D=shkc&market_cd%5B%5D=szmb&market_cd%5B%5D=szcy&btype=&listed=Y&qflag=N&sw_cd=&bond_ids=&rp=50&page=1",//必填項
 8     web=Text.FromBinary(Web.Contents(url,[Headers=headers,Query=query,Content=Text.ToBinary(content)])),
 9     #"解析的 JSON" = Json.Document(web),
10     rows = #"解析的 JSON"[rows],
11     轉(zhuǎn)換為表 = Table.FromList(rows, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
12     #"展開的“Column1”" = Table.ExpandRecordColumn(轉(zhuǎn)換為表, "Column1", {"id", "cell"}, {"Column1.id", "Column1.cell"}),
13     #"展開的“Column1.cell”" = Table.ExpandRecordColumn(#"展開的“Column1”", "Column1.cell", {"bond_id", "bond_nm", "bond_py", "price", "increase_rt", "stock_id", "stock_nm", "stock_py", "sprice", "sincrease_rt", "pb", "convert_price", "convert_value", "convert_dt", "premium_rt", "bond_premium_rt", "dblow", "adjust_condition", "sw_cd", "market_cd", "btype", "list_dt", "qflag2", "owned", "hold", "bond_value", "rating_cd", "option_value", "volatility_rate", "put_convert_price", "force_redeem_price", "convert_amt_ratio", "fund_rt", "short_maturity_dt", "year_left", "curr_iss_amt", "volume", "svolume", "turnover_rt", "ytm_rt", "put_ytm_rt", "notes", "noted", "bond_nm_tip", "redeem_icon", "last_time", "qstatus", "margin_flg", "sqflag", "pb_flag", "adj_cnt", "adj_scnt", "convert_price_valid", "convert_price_tips", "convert_cd_tip", "ref_yield_info", "adjusted", "orig_iss_amt", "price_tips", "redeem_dt", "real_force_redeem_price", "option_tip", "adjust_status", "unadj_cnt"}, {"Column1.cell.bond_id", "Column1.cell.bond_nm", "Column1.cell.bond_py", "Column1.cell.price", "Column1.cell.increase_rt", "Column1.cell.stock_id", "Column1.cell.stock_nm", "Column1.cell.stock_py", "Column1.cell.sprice", "Column1.cell.sincrease_rt", "Column1.cell.pb", "Column1.cell.convert_price", "Column1.cell.convert_value", "Column1.cell.convert_dt", "Column1.cell.premium_rt", "Column1.cell.bond_premium_rt", "Column1.cell.dblow", "Column1.cell.adjust_condition", "Column1.cell.sw_cd", "Column1.cell.market_cd", "Column1.cell.btype", "Column1.cell.list_dt", "Column1.cell.qflag2", "Column1.cell.owned", "Column1.cell.hold", "Column1.cell.bond_value", "Column1.cell.rating_cd", "Column1.cell.option_value", "Column1.cell.volatility_rate", "Column1.cell.put_convert_price", "Column1.cell.force_redeem_price", "Column1.cell.convert_amt_ratio", "Column1.cell.fund_rt", "Column1.cell.short_maturity_dt", "Column1.cell.year_left", "Column1.cell.curr_iss_amt", "Column1.cell.volume", "Column1.cell.svolume", "Column1.cell.turnover_rt", "Column1.cell.ytm_rt", "Column1.cell.put_ytm_rt", "Column1.cell.notes", "Column1.cell.noted", "Column1.cell.bond_nm_tip", "Column1.cell.redeem_icon", "Column1.cell.last_time", "Column1.cell.qstatus", "Column1.cell.margin_flg", "Column1.cell.sqflag", "Column1.cell.pb_flag", "Column1.cell.adj_cnt", "Column1.cell.adj_scnt", "Column1.cell.convert_price_valid", "Column1.cell.convert_price_tips", "Column1.cell.convert_cd_tip", "Column1.cell.ref_yield_info", "Column1.cell.adjusted", "Column1.cell.orig_iss_amt", "Column1.cell.price_tips", "Column1.cell.redeem_dt", "Column1.cell.real_force_redeem_price", "Column1.cell.option_tip", "Column1.cell.adjust_status", "Column1.cell.unadj_cnt"}),
14     刪除的列 = Table.RemoveColumns(#"展開的“Column1.cell”",{"Column1.id"}),
15     重命名的列 = Table.RenameColumns(刪除的列,{{"Column1.cell.bond_id", "代碼"}, {"Column1.cell.bond_nm", "轉(zhuǎn)債名稱"}}),
16     重命名的列1 = Table.RenameColumns(重命名的列,{{"Column1.cell.price", "現(xiàn)價"}, {"Column1.cell.increase_rt", "漲跌幅"}}),
17     重命名的列2 = Table.RenameColumns(重命名的列1,{{"Column1.cell.stock_nm", "正股名稱"}}),
18     刪除的列3 = Table.RemoveColumns(重命名的列2,{"Column1.cell.stock_py"}),
19     重命名的列3 = Table.RenameColumns(刪除的列3,{{"Column1.cell.sprice", "正股價"}, {"Column1.cell.sincrease_rt", "正股漲跌"}, {"Column1.cell.pb", "正股PB"}, {"Column1.cell.convert_price", "轉(zhuǎn)股價"}, {"Column1.cell.convert_value", "轉(zhuǎn)股價值"}}),
20     排序的行 = Table.Sort(重命名的列3,{{"代碼", Order.Ascending}}),
21     刪除的列4 = Table.RemoveColumns(排序的行,{"Column1.cell.convert_dt"}),
22     重命名的列4 = Table.RenameColumns(刪除的列4,{{"Column1.cell.premium_rt", "轉(zhuǎn)股溢價率(foo)"}, {"Column1.cell.dblow", "雙低"}, {"Column1.cell.rating_cd", "債券評級"}, {"Column1.cell.short_maturity_dt", "到期時間"}, {"Column1.cell.year_left", "剩余年限"}, {"Column1.cell.curr_iss_amt", "剩余規(guī)模（億元）"}, {"Column1.cell.volume", "轉(zhuǎn)債成交額（萬元）"}, {"Column1.cell.turnover_rt", "換手率"}, {"Column1.cell.ytm_rt", "到期稅前收益"}, {"Column1.cell.bond_nm_tip", "強贖"}, {"Column1.cell.convert_price_tips", "下修"}, {"Column1.cell.convert_cd_tip", "轉(zhuǎn)股期"}, {"Column1.cell.list_dt", "上市日"}, {"Column1.cell.last_time", "數(shù)據(jù)更新時間"}}),
23     刪除的列5 = Table.RemoveColumns(重命名的列4,{"Column1.cell.sw_cd", "Column1.cell.market_cd", "Column1.cell.btype", "Column1.cell.qflag2", "Column1.cell.owned", "Column1.cell.hold", "Column1.cell.bond_value", "Column1.cell.option_value"}),
24     重命名的列5 = Table.RenameColumns(刪除的列5,{{"Column1.cell.put_convert_price", "回售觸發(fā)價"}, {"Column1.cell.force_redeem_price", "強贖觸發(fā)價"}, {"Column1.cell.convert_amt_ratio", "轉(zhuǎn)債占比"}}),
25     刪除的列6 = Table.RemoveColumns(重命名的列5,{ "Column1.cell.put_ytm_rt", "Column1.cell.noted"}),
26     重命名的列6 = Table.RenameColumns(刪除的列6,{{"Column1.cell.redeem_icon", "不提前贖回提示"}, {"Column1.cell.orig_iss_amt", "發(fā)行規(guī)模"}}),
27     刪除的列7 = Table.RemoveColumns(重命名的列6,{"Column1.cell.volatility_rate", "Column1.cell.notes"}),
28     重命名的列7 = Table.RenameColumns(刪除的列7,{{"Column1.cell.redeem_dt", "強贖日期"}, {"Column1.cell.real_force_redeem_price", "強贖價格"}, {"Column1.cell.bond_py", "轉(zhuǎn)債簡寫"}, {"Column1.cell.stock_id", "正股代碼"}, {"Column1.cell.margin_flg", "融資融券標的"}}),
29     已添加自定義 = Table.AddColumn(重命名的列7, "轉(zhuǎn)股溢價率", each Number.ToText([#"轉(zhuǎn)股溢價率(foo)"]/100,"P2")),
30     重排序的列 = Table.ReorderColumns(已添加自定義,{"代碼", "轉(zhuǎn)債名稱", "轉(zhuǎn)債簡寫", "現(xiàn)價", "漲跌幅", "正股代碼", "正股名稱", "正股價", "正股漲跌", "正股PB", "轉(zhuǎn)股價", "轉(zhuǎn)股價值", "轉(zhuǎn)股溢價率(foo)", "轉(zhuǎn)股溢價率", "雙低", "上市日", "債券評級", "回售觸發(fā)價", "強贖觸發(fā)價", "轉(zhuǎn)債占比", "到期時間", "剩余年限", "剩余規(guī)模（億元）", "轉(zhuǎn)債成交額（萬元）", "Column1.cell.svolume", "換手率", "到期稅前收益", "強贖", "不提前贖回提示", "數(shù)據(jù)更新時間", "Column1.cell.qstatus", "融資融券標的", "Column1.cell.sqflag", "Column1.cell.pb_flag", "Column1.cell.adj_cnt", "Column1.cell.adj_scnt", "Column1.cell.convert_price_valid", "下修", "轉(zhuǎn)股期", "Column1.cell.ref_yield_info", "Column1.cell.adjusted", "發(fā)行規(guī)模", "Column1.cell.price_tips", "強贖日期", "強贖價格", "Column1.cell.option_tip"}),
31     刪除的列1 = Table.RemoveColumns(重排序的列,{"轉(zhuǎn)股溢價率(foo)"}),
32     更改的類型 = Table.TransformColumnTypes(刪除的列1,{{"轉(zhuǎn)股溢價率", Percentage.Type}}),
33     已添加自定義1 = Table.AddColumn(更改的類型, "剩余規(guī)模 × 溢價率", each [#"剩余規(guī)模（億元）"]*[轉(zhuǎn)股溢價率]),
34     已添加自定義2 = Table.AddColumn(已添加自定義1, "三低評分", each [現(xiàn)價]*(1+[轉(zhuǎn)股溢價率])*[#"剩余規(guī)模（億元）"]),
35     更改的類型1 = Table.TransformColumnTypes(已添加自定義2,{{"三低評分", Int64.Type}}),
36     重命名的列8 = Table.RenameColumns(更改的類型1,{{"Column1.cell.fund_rt", "機構(gòu)持倉占比"}, {"Column1.cell.price_tips", "轉(zhuǎn)債價格提示"}, {"Column1.cell.bond_premium_rt", "債券溢價率"}, {"Column1.cell.adjust_condition", "下修天計數(shù)"}, {"Column1.cell.svolume", "正股成交額（萬元）"}})
37 in
38     重命名的列8