+ Reply to Thread
Results 1 to 20 of 20

"User Agent" and "Cookies" options are not there in Advanced Search options for Web query.

  1. #1
    Forum Contributor
    Join Date
    12-11-2012
    Location
    Saudi Arabia
    MS-Off Ver
    Excel 2016
    Posts
    290

    "User Agent" and "Cookies" options are not there in Advanced Search options for Web query.

    Is there a way to get this option in the Excel 2016, as I am not able to get queries from the NSE ( national Sock Exchange of India) option chain (https://www1.nseindia.com/live_marke...date=30APR2020).
    Does anyone know how to do this ? Since this is a mind boggling scenario, I would like to thank in advance to those who make an attempt to get this solution( for their time,effort and kindness)

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,720

    Re: "User Agent" and "Cookies" options are not there in Advanced Search options for Web qu

    I assume you are using From Web using Get & Transform Data tab.

    In order to query from JSP (Java Server Page) you'll need to pass on various header using "Advanced" option.

    First navigate to site and use your browser's Developer tool to observe the request sent to the server.
    0.JPG
    Then hit "From Web" and choose Advanced.

    There you will supply required headers.
    Accept, Cookie, Accept-Encoding, Accept-Language, User-Agent. Some may not be required, but I didn't test.
    EDIT: If header option isn't in the dropdown, you can simply type your custom header name.

    Once connection is made, you'll pick the table as your data source.
    1.JPG

    Complete M query as reference.
    Please Login or Register  to view this content.
    Last edited by CK76; 04-17-2020 at 09:40 AM.
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  3. #3
    Forum Contributor
    Join Date
    12-11-2012
    Location
    Saudi Arabia
    MS-Off Ver
    Excel 2016
    Posts
    290

    Re: "User Agent" and "Cookies" options are not there in Advanced Search options for Web qu

    Thanks for your effort. It looks like something workable but I dont understand anything, as I dont know anything about these things. Kindly upload a file so that the mind boggling problem comes to an end.

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,720

    Re: "User Agent" and "Cookies" options are not there in Advanced Search options for Web qu

    Just paste in the M query code in Query editor's Advanced Editor mode.

    You may need to change cookie string.

    Do a search on "Get & Transform, From Web, Advanced" and you should find several tutorials and examples that you can follow.

  5. #5
    Forum Contributor
    Join Date
    12-11-2012
    Location
    Saudi Arabia
    MS-Off Ver
    Excel 2016
    Posts
    290

    Re: "User Agent" and "Cookies" options are not there in Advanced Search options for Web qu

    For last three days I am working on this and trying to see many videos and read many things but it just does not enter into my thick skull !
    I have been able to get the details from that Networking page as under
    MAIN URL = https://www.nseindia.com/api/option-...s?symbol=NIFTY

    Accept- Encoding = gzip, deflate, br

    Accept -Language = en-US,en;q=0.9

    UserAgent = Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.163 Safari/537.36

    SEt-Cookie = AA80B1EEBE0BCE9DCE0838A5D748D4C5~D4uiQNXakpG6ZCRp576KI9QSS1OpfHU5dCdmVqPD100lB6omFlAOek6SFCIfwq7rZS3Eys+k78bkpZYcrpTWYnlCHXfpzciKgaQGJCfY33k4NuZf/M86dmyr9n68NfrlZ5JOT+3jG3M01brffTJvJB97RxnzkApCn7emltGk1FY

    But my excel 2016 advance options does not give me the option to choose "User Agent" and "Cookies" or "Set-Cookies" options.

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,720

    Re: "User Agent" and "Cookies" options are not there in Advanced Search options for Web qu

    In header section. Just type in what you need.

    There's no dropdown for some of the header as I noted in my post.

  7. #7
    Forum Contributor
    Join Date
    12-11-2012
    Location
    Saudi Arabia
    MS-Off Ver
    Excel 2016
    Posts
    290

    Re: "User Agent" and "Cookies" options are not there in Advanced Search options for Web qu

    Yes that is a great simple idea, but it is giving me an error :- Details: "The header value is not supported. Supported values are: gzip, deflate, identity." The actual value that the system is giving me is :- gzip, deflate, br

  8. #8
    Forum Contributor
    Join Date
    12-11-2012
    Location
    Saudi Arabia
    MS-Off Ver
    Excel 2016
    Posts
    290

    Re: "User Agent" and "Cookies" options are not there in Advanced Search options for Web qu

    But from what I see above, you were able to get the values in your file. It will be much more simpler if you could kindly upload your file.

  9. #9
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,720

    Re: "User Agent" and "Cookies" options are not there in Advanced Search options for Web qu

    Just remove ", br" from your header value. br isn't one of supported value.

    If you need to replicate my setup, go into Query Editor -> View -> Advanced Editor. Then paste in the M query code. This will replicate my set up.
    No need for file upload.

  10. #10
    Forum Contributor
    Join Date
    12-11-2012
    Location
    Saudi Arabia
    MS-Off Ver
    Excel 2016
    Posts
    290

    Re: "User Agent" and "Cookies" options are not there in Advanced Search options for Web qu

    It is sad to know that people who are experts and adepts like you DO NOT REALIZE that there are ignorant people like me who know NOTHING.
    Sadly I have spent another 1 hr looking for that M Query section where I can put your code but I dont find it and so, despite all your help, time and effort I hv given up.

  11. #11
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,720

    Re: "User Agent" and "Cookies" options are not there in Advanced Search options for Web qu

    ? Just follow my instruction the the letter.

    I'm willing to help, but not do all the work for you. You'll need to carefully follow instruction provided.

    To get to advanced editor... just google "Power Query Advanced Editor" and first 2 are MS article.
    Ex:
    https://support.microsoft.com/en-us/...rs=en-us&ad=us

  12. #12
    Forum Contributor
    Join Date
    12-11-2012
    Location
    Saudi Arabia
    MS-Off Ver
    Excel 2016
    Posts
    290

    Re: "User Agent" and "Cookies" options are not there in Advanced Search options for Web qu

    Thanks for your help and guidance. I finally did find out a blank query and went to advanced and pasted your data, but strangely it is still giving me timeout error despite the fact that my internet is working well. What do you think might be causing the problem ?

  13. #13
    Forum Contributor
    Join Date
    12-11-2012
    Location
    Saudi Arabia
    MS-Off Ver
    Excel 2016
    Posts
    290

    Re: "User Agent" and "Cookies" options are not there in Advanced Search options for Web qu

    Kindly take a look at the attached file
    Attached Files Attached Files

  14. #14
    Forum Contributor
    Join Date
    12-11-2012
    Location
    Saudi Arabia
    MS-Off Ver
    Excel 2016
    Posts
    290

    Re: "User Agent" and "Cookies" options are not there in Advanced Search options for Web qu

    Strange ! after closing the file and reopening it it has called the full table ..... I am checking it

  15. #15
    Forum Contributor
    Join Date
    12-11-2012
    Location
    Saudi Arabia
    MS-Off Ver
    Excel 2016
    Posts
    290

    Re: "User Agent" and "Cookies" options are not there in Advanced Search options for Web qu

    Yes it is working perfectly.

    Thanks a lot of your patience, time and effort to teach.

    I am reminded of the old saying " Give me a fish and I will eat it today; Teach me how to fish and I shall eat fish all my life"

    Thanks a ton again !

  16. #16
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,720

    Re: "User Agent" and "Cookies" options are not there in Advanced Search options for Web qu

    You are welcome, glad you got it working

  17. #17
    Forum Contributor
    Join Date
    12-11-2012
    Location
    Saudi Arabia
    MS-Off Ver
    Excel 2016
    Posts
    290

    Re: "User Agent" and "Cookies" options are not there in Advanced Search options for Web qu

    I have a humble request CK76. Can we transform your M code given above to a macro, which can then be triggered at will by a stand alone button ?
    I will be really grateful if you can kindly provide it here.
    Thanks.

  18. #18
    Forum Contributor
    Join Date
    12-11-2012
    Location
    Saudi Arabia
    MS-Off Ver
    Excel 2016
    Posts
    290

    Re: "User Agent" and "Cookies" options are not there in Advanced Search options for Web qu

    Dear CK76,
    for some strange reason, the file attached above has stopped working!
    Could you please tel why, and what correction shall we make ?

  19. #19
    Forum Contributor
    Join Date
    12-11-2012
    Location
    Saudi Arabia
    MS-Off Ver
    Excel 2016
    Posts
    290

    Re: "User Agent" and "Cookies" options are not there in Advanced Search options for Web qu

    PLease read the statement above and help me if you can.

  20. #20
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,448

    Re: "User Agent" and "Cookies" options are not there in Advanced Search options for Web qu

    How can anyone help without the workbook that has stopped working? We are not psychic!

    You might wish to remove the solved tag from the thread for the time being.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Histogram - "Text Options" -->"Textbox" is greyed out.
    By TendingThyWounds in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-10-2019, 05:28 PM
  2. Can't create "Secondary Dependent" DropLists listing "Unused" options
    By Ochimus in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-13-2019, 05:17 PM
  3. Requiring input by user to be either "Economy", "Business" or "Club"
    By cmurda in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-08-2014, 08:57 PM
  4. [SOLVED] Emulate Excel 97 getopenfilename "Advanced" find including "Search subfolders"
    By FORTRANguru in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-06-2014, 08:21 PM
  5. Replies: 6
    Last Post: 02-25-2013, 03:16 AM
  6. Making the Tools>Options...Calculation "Manua"l Vs "Automatic" a Button.
    By Ecoich in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-20-2007, 07:50 PM
  7. [SOLVED] EXCEL allow 2 options on status bar e.g. show "Count" + "Sum"
    By LEJM in forum Excel General
    Replies: 1
    Last Post: 08-15-2006, 06:50 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1