Page 2 of 3 FirstFirst 1 2 3 LastLast
Results 11 to 20 of 23

Thread: The Web Query Table data in excel not the same as displayed online at the website.

  1. #11
    Seeker guanstan's Avatar
    Join Date
    Dec 2019
    Posts
    13
    Articles
    0
    Excel Version
    2016


    Register for a FREE account, and/
    or Log in to avoid these ads!

    Just pick any one row in my 50 entries/page and compare to your attached file (same row) and you see they are not the same. This only happens in my 50 entries/page attached file. The 10 entries/page and 20 entries/page has no problem. I thinks the problem could be the EXCEL2016 software in my laptop. Somehow if I do the web query for 50 entries/page the data is not right.
    Anyway many thanks for your help. Appreciate it very much.

  2. #12
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,338
    Articles
    0
    Excel Version
    Office 365 Subscription
    I cannot see any difference. You are the one asking for help: all I have asked is that you provide a version with a few specific anomalies highlighted so that I can cross-check them quickly and easily. If you do this, I'll take a look. As I said before, I am not going to go hunting for them - sorry.
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #13
    Seeker guanstan's Avatar
    Join Date
    Dec 2019
    Posts
    13
    Articles
    0
    Excel Version
    2016
    Your attached file (PQ Web Scrape AliGW.xlsx) No:1 Code: 0012 Name: 3A[s] Last Done: 0.74 Vol ('00): 2258
    My attached file (50E.xlsx) No:1 Code: 0012 Name: 3A[s] Last Done: 0.745 Vol ('00): 314
    see the data of the columns Last Done: and the Vol ('00): is different.
    Look at specifically the Last Done: and Vol ('00): columns for other table-rows you see the difference.
    Last edited by guanstan; 2019-12-15 at 09:58 AM.

  4. #14
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,338
    Articles
    0
    Excel Version
    Office 365 Subscription
    That's because you haven't refreshed the query. I have opened both our workbooks and refreshed the queries. Mine now shows:

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    1
    No Name Code REM Last Done LACP CHG %CHG Vol ('00) BUY Vol ('00) BUY SELL SELL Vol ('00) HIGH LOW stock_id
    2
    1
    3A [S] 0012 s
    0.74
    0.745
    -0.005 -0.67 2,258 308 0.740 0.750 951
    0.75
    0.74
    0012
    3
    2
    AASIA [S] 7054 s
    0.155
    0.165
    -0.010 -6.06 31,274 85 0.155 0.160 4,302
    0.17
    0.155
    7054
    4
    3
    AAX [S] 5238 s
    0.15
    0.155
    -0.005 -3.23 20,495 114,187 0.150 0.155 64,063
    0.155
    0.15
    5238
    5
    4
    AAX-WA [S] 5238WA s
    0.005
    0.005
    - - 3,058 15,689 0.005 0.010 204,277
    0.01
    0.005
    5238WA
    Sheet: Sheet2

    and yours shows:

    Excel 2016 (Windows) 32 bit
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    Q
    R
    S
    3
    No Name Code REM Last Done LACP CHG %CHG Vol ('00) BUY Vol ('00) BUY SELL SELL Vol ('00) HIGH LOW stock_id
    4
    1
    3A [S] 0012 s
    0.74
    0.745
    -0.005 -0.67 2,258 308 0.740 0.750 951
    0.75
    0.74
    0012
    5
    2
    AASIA [S] 7054 s
    0.155
    0.165
    -0.010 -6.06 31,274 85 0.155 0.160 4,302
    0.17
    0.155
    7054
    6
    3
    AAX [S] 5238 s
    0.15
    0.155
    -0.005 -3.23 20,495 114,187 0.150 0.155 64,063
    0.155
    0.15
    5238
    7
    4
    AAX-WA [S] 5238WA s
    0.005
    0.005
    - - 3,058 15,689 0.005 0.010 204,277
    0.01
    0.005
    5238WA
    Sheet: Sheet1

    You can set your query to refresh when you open the workbook, if you wish. It seems you don't really understand how PowerQuery works.
    Ali
    Enthusiastic self-taught user of MS Excel!

  5. #15
    Seeker guanstan's Avatar
    Join Date
    Dec 2019
    Posts
    13
    Articles
    0
    Excel Version
    2016
    Yes you are right. I just started this week to use PowerQuery thru articles in the internet here and there. Anyway thanks for your help. I know next to nothing about PowerQuery but learning...I am not tech-savvy.
    Last edited by guanstan; 2019-12-15 at 10:56 AM.

  6. #16
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,338
    Articles
    0
    Excel Version
    Office 365 Subscription
    OK - so you need to refresh queries! Either do this manually (Data ribbon - Refresh (All)), or right-click your query in the query bar on the right (Data ribbon - Queries & Connections - right-click - Properties - Refresh query when opening file).

    I asked you several posts back if you had refreshed the query - it would have been sensible to ask what I meant if you weren't sure - it would have saved a lot of time! I've been learning PQ now for a few years.
    Ali
    Enthusiastic self-taught user of MS Excel!

  7. #17
    Seeker guanstan's Avatar
    Join Date
    Dec 2019
    Posts
    13
    Articles
    0
    Excel Version
    2016
    Before I wrote in for help, I have done REFRESH query and compare the EXCEL table to the online Bursa WEB page. They don't match. Previously I used to copy the online BURSA WEB (998 entries) in one page and paste it into EXCEL. But recently BURSA made changes (limit 50 entries per page) which make my old way of copying and paste not practical. That's why I embark to use PowerQuery.

  8. #18
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,338
    Articles
    0
    Excel Version
    Office 365 Subscription
    That's impossible if you did the refresh correctly, I'm afraid, so it comes down to novice user error, one way or the other. Either way, you have learned something today, and that's all that matters.
    Ali
    Enthusiastic self-taught user of MS Excel!

  9. #19
    Seeker guanstan's Avatar
    Join Date
    Dec 2019
    Posts
    13
    Articles
    0
    Excel Version
    2016
    Tomorrow(6 pm my time) I'll REFRESH my 50E.xlsx and compare to online BURSA WEB to see if they match. Thank you for your help. Funny for my 20E.xlsx and 10E.xlsx web queries the data matches - no refresh no problem.
    Last edited by guanstan; 2019-12-15 at 11:46 AM.

  10. #20
    Seeker guanstan's Avatar
    Join Date
    Dec 2019
    Posts
    13
    Articles
    0
    Excel Version
    2016
    I did the REFRESH for the 50E.xlsx today. The data don't match with online BURSA WEB page. I did a new workbook with all 3 queries (10, 20 & 50 entries/page) in one worksheet (Sheet 1) - REFRESHed ALL at same time. Also did a copy & paste into another worksheet (Sheet 2) the data from online BURSA WEB page. They ALL don't match.

    Maybe I did not do them right. I attach the files for your reference.
    Attached Files Attached Files

Page 2 of 3 FirstFirst 1 2 3 LastLast

Posting Permissions

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