用论坛的案例获取JSL网页提示游客登录无法获取全部数据

bobonone

我在论坛帖子http://club.excelhome.net/thread-1536860-1-1.html中根据大神deadzlq的“PQ爬取【可转债 - 可转债 - 集思录】数据.rar”用来获取JSL的可转债数据，之前一直用的好好的，今天开始只能获取前30的数据， ，看了显示现在需要登录才能显示完整信息，不过自己是个菜鸟只会抄大神的代码，也不知道如何才能添加cookie，还请各位大神不吝赐教，谢谢 PQ爬取【可转债 - 可转债 - 集思录】数据.rar (183.77 KB, 下载次数: 3)

2021-7-27 17:13 上传

点击文件名下载附件

1. let
   
2.     Timestamp=Text.Start(Text.From(Duration.TotalSeconds(DateTime.LocalNow()-#datetime(1970,1,1,8,0,0))*1000),13),
   
3.     Url="https://www.jisilu.cn/data/cbnew/cb_list/?___jsl=LST___t=" & Timestamp,
   
4.     Source = Table.FromRecords(Json.Document(Web.Contents(Url,[Headers=[Referer="https://www.jisilu.cn/data/cbnew/"]]))[rows]),
   
5.     getCols = List.Distinct(List.Combine(List.Transform(Source[cell],Record.FieldNames))),
   
6.     Result = Table.ExpandRecordColumn(Source , "cell", getCols ,getCols ),
   
7.     删除的列 = Table.RemoveColumns(Result,{"id"}),
   
8.     重排序的列 = Table.ReorderColumns(删除的列,{"bond_id", "bond_nm", "price", "stock_id", "stock_nm", "btype", "convert_price", "convert_price_valid_from", "convert_dt", "maturity_dt", "next_put_dt", "put_dt", "put_notes", "put_price", "put_inc_cpn_fl", "put_convert_price_ratio", "put_count_days", "put_total_days", "put_real_days", "repo_discount_rt", "repo_valid_from", "repo_valid_to", "turnover_rt", "pb", "redeem_price", "redeem_inc_cpn_fl", "redeem_price_ratio", "redeem_count_days", "redeem_total_days", "redeem_real_days", "redeem_dt", "redeem_flag", "orig_iss_amt", "curr_iss_amt", "rating_cd", "issuer_rating_cd", "guarantor", "ssc_dt", "esc_dt", "sc_notes", "market_cd", "force_redeem", "real_force_redeem_price", "convert_cd", "repo_cd", "ration", "ration_cd", "apply_cd", "online_offline_ratio", "qflag", "qflag2", "ration_rt", "fund_rt", "margin_flg", "lt_bps", "pb_flag", "total_shares", "float_shares", "sqflg", "sprice", "svolume", "sincrease_rt", "qstatus", "bond_value", "bond_value2", "volatility_rate", "last_time", "convert_value", "premium_rt", "year_left", "ytm_rt", "ytm_rt_tax", "full_price", "increase_rt", "volume", "convert_price_valid", "adj_scnt", "adj_cnt", "redeem_icon", "ref_yield_info", "adjust_tip", "adjusted", "option_tip", "bond_value3", "left_put_year", "short_maturity_dt", "dblow", "force_redeem_price", "put_convert_price", "convert_amt_ratio", "convert_amt_ratio2", "convert_amt_ratio_tips", "stock_net_value", "stock_cd", "pre_bond_id", "repo_valid", "convert_cd_tip", "price_tips"}),
   
9.     删除的列1 = Table.RemoveColumns(重排序的列,{"stock_id"}),
   
10.     重排序的列1 = Table.ReorderColumns(删除的列1,{"bond_id", "bond_nm", "price", "increase_rt", "stock_cd", "stock_nm", "sprice", "sincrease_rt", "convert_price", "premium_rt", "pb", "rating_cd", "curr_iss_amt", "year_left", "maturity_dt", "ytm_rt", "convert_amt_ratio", "dblow", "btype", "convert_price_valid_from", "convert_dt", "next_put_dt", "put_dt", "put_notes", "put_price", "put_inc_cpn_fl", "put_convert_price_ratio", "put_count_days", "put_total_days", "put_real_days", "repo_discount_rt", "repo_valid_from", "repo_valid_to", "turnover_rt", "redeem_price", "redeem_inc_cpn_fl", "redeem_price_ratio", "redeem_count_days", "redeem_total_days", "redeem_real_days", "redeem_dt", "redeem_flag", "orig_iss_amt", "issuer_rating_cd", "guarantor", "ssc_dt", "esc_dt", "sc_notes", "market_cd", "force_redeem", "real_force_redeem_price", "convert_cd", "repo_cd", "ration", "ration_cd", "apply_cd", "online_offline_ratio", "qflag", "qflag2", "ration_rt", "fund_rt", "margin_flg", "lt_bps", "pb_flag", "total_shares", "float_shares", "sqflg", "svolume", "qstatus", "bond_value", "bond_value2", "volatility_rate", "last_time", "convert_value", "ytm_rt_tax", "full_price", "volume", "convert_price_valid", "adj_scnt", "adj_cnt", "redeem_icon", "ref_yield_info", "adjust_tip", "adjusted", "option_tip", "bond_value3", "left_put_year", "short_maturity_dt", "force_redeem_price", "put_convert_price", "convert_amt_ratio2", "convert_amt_ratio_tips", "stock_net_value", "pre_bond_id", "repo_valid", "convert_cd_tip", "price_tips"}),
    
11.     删除的列2 = Table.RemoveColumns(重排序的列1,{"btype", "convert_price_valid_from", "convert_dt", "next_put_dt", "put_dt", "put_notes", "put_price", "put_inc_cpn_fl", "put_convert_price_ratio", "put_count_days", "put_total_days", "put_real_days", "repo_discount_rt", "repo_valid_from", "repo_valid_to", "turnover_rt", "redeem_price", "redeem_inc_cpn_fl", "redeem_price_ratio", "redeem_count_days", "redeem_total_days", "redeem_real_days", "redeem_dt", "redeem_flag", "orig_iss_amt", "issuer_rating_cd", "guarantor", "ssc_dt", "esc_dt", "sc_notes", "market_cd", "force_redeem", "real_force_redeem_price", "convert_cd", "repo_cd", "ration", "ration_cd", "apply_cd", "online_offline_ratio", "qflag", "qflag2", "ration_rt", "fund_rt", "margin_flg", "lt_bps", "pb_flag", "total_shares", "float_shares", "sqflg", "svolume", "qstatus", "bond_value", "bond_value2", "volatility_rate", "last_time", "convert_value", "ytm_rt_tax", "full_price", "volume", "convert_price_valid", "adj_scnt", "adj_cnt", "redeem_icon", "ref_yield_info", "adjust_tip", "adjusted", "option_tip", "bond_value3", "left_put_year", "short_maturity_dt", "force_redeem_price", "put_convert_price", "convert_amt_ratio2", "convert_amt_ratio_tips", "stock_net_value", "pre_bond_id", "repo_valid", "convert_cd_tip", "price_tips"}),
    
12.     排序的行 = Table.Sort(删除的列2,{{"dblow", Order.Ascending}}),
    
13.     更改的类型 = Table.TransformColumnTypes(排序的行,{{"price", type number}, {"increase_rt", Percentage.Type}, {"sprice", type number}, {"sincrease_rt", Percentage.Type}, {"convert_price", type number}, {"premium_rt", Percentage.Type}, {"pb", type number}, {"curr_iss_amt", type number}, {"year_left", type number}, {"maturity_dt", type date}, {"ytm_rt", Percentage.Type}, {"convert_amt_ratio", Percentage.Type}, {"dblow", type number}}),
    
14.     重命名的列 = Table.RenameColumns(更改的类型,{{"bond_id", "转债代码"}, {"bond_nm", "转债名称"}, {"price", "现价"}, {"increase_rt", "涨跌幅"}, {"stock_cd", "正股代码"}, {"stock_nm", "正股名称"}, {"sprice", "正股价"}, {"sincrease_rt", "正股涨跌"}, {"convert_price", "转股价"}, {"premium_rt", "溢价率"}, {"rating_cd", "评级"}, {"curr_iss_amt", "剩余规模"}, {"year_left", "剩余年限"}, {"maturity_dt", "到期日期"}, {"ytm_rt", "到期税前收益"}, {"convert_amt_ratio", "转债占比"}, {"dblow", "双低值"}}),
    
15.     排序的行1 = Table.Sort(重命名的列,{{"双低值", Order.Ascending}})
    
16. in
    
17.     排序的行1

复制代码

hyzhangyong

= Json.Document(Web.Contents("https://myapp.example.com/api/test.php", [Headers=[Cookie="PHPSESSID=123abc"]]))

bobonone

hyzhangyong 发表于 2021-7-27 19:11
= Json.Document(Web.Contents("https://myapp.example.com/api/test.php", [Headers=[Cookie="PHPSESSID=1 ...

谢谢大神指教，可惜是个小白，不明白哪个是cookie， ，请问是这样改吗= Json.Document(Web.Contents("https://www.jisilu.cn/data/cbnew/cb_list/?", [Headers=[Cookie="PHPSESSID=*****"]]))

hyzhangyong

打开网页，按F12,刷新一下，如图所示就是cookie