How to get data using Power Query

G

GAURAV SEMWAL

New Member
Joined
Oct 19, 2022
Messages
30
Office Version
  1. 365
Platform
  1. Windows
1666540457167.png

https://archives.nseindia.com/content/historical/DERIVATIVES/2022/OCT/fo21OCT2022bhav.csv.zip

How to get data from this link using Power Query as per selection of date .
Earlier i was using that below given process but it is not working to this link.
1666540416372.png
 
Last edited by a moderator:
Sort by date Sort by votes
smozgur said:
As I explained - I don't think you have all the corresponding columns in the CSV file.
And again, click on the Imported CSV step and see the columns that you have. The previously used XLS file might have contained all the columns that you need so the previous implementation might have been built on that particular data structure, however, the current CSV file doesn't have all columns that in the requested format.
Click to expand...
sir after changing in code there in synrax error
1666546526456.png
 
Upvote 0

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
@GAURAV SEMWAL: I am trying to help you.

However, I really can't type your code to test it.

If you could post the original code as text, not pictured, (before changing it as I have no idea what you changed), then I and I believe another helper would be happy to try to assist you.

Hope it makes sense.
 
Upvote 0
smozgur said:
@GAURAV SEMWAL: I am trying to help you.

However, I really can't type your code to test it.

If you could post the original code as text, not pictured, (before changing it as I have no idea what you changed), then I and I believe another helper would be happy to try to assist you.

Hope it makes sense.
Click to expand...
sir i am not able use xl2bb properly and it is not working at my side so is there different mode by which i can share the code.
1666548235176.png
 
Upvote 0
You don’t need XL2BB to post codes.

Go to the advanced editor in Power Query, copy everything in the editor (code starting with “let”) and paste here.

Advanced Editor can be opened by clicking on the Advanced Editor button that you can see on the ribbon.
 
Upvote 0
smozgur said:
Advanced Editor can be opened by clicking on the Advanced Editor button that you can see on the ribbon.
Click to expand...
Power Query: 
let
   
    Source = Web.Contents("[URL]https://archives.nseindia.com/content/historical/DERIVATIVES/2022/OCT/fo21OCT2022bhav.csv.zip[/URL]"),
        UnzipContents=(ZIPFile) =>
    let
        Header = BinaryFormat.Record([
            MiscHeader = BinaryFormat.Binary(14),
            BinarySize = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
            FileSize   = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
            FileNameLen= BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian),
            ExtrasLen  = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian)   
        ]),
        HeaderChoice = BinaryFormat.Choice(
            BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
            each if _ <> 67324752             // not the IsValid number? then return a dummy formatter
                then BinaryFormat.Record([IsValid = false, Filename=null, Content=null])
                else BinaryFormat.Choice(
                        BinaryFormat.Binary(26),      // Header payload - 14+4+4+2+2
                        each BinaryFormat.Record([
                            IsValid  = true,
                            Filename = BinaryFormat.Text(Header(_)[FileNameLen]),
                            Extras   = BinaryFormat.Text(Header(_)[ExtrasLen]),
                            Content  = BinaryFormat.Transform(
                                BinaryFormat.Binary(Header(_)[BinarySize]),
                                (x) => try Binary.Buffer(Binary.Decompress(x, Compression.Deflate)) otherwise null
                            )
                            ]),
                            type binary                   // enable streaming
                    )
        ),
        ZipFormat = BinaryFormat.List(HeaderChoice, each _[IsValid] = true),
        Entries = List.Transform(
        List.RemoveLastN( ZipFormat(ZIPFile), 1),
        (e) => [FileName = e[Filename], Content = e[Content] ]
        )
    in
        Table.FromRecords(Entries),
        //Function End
    Files = UnzipContents(Source),
    // Function End
    Content = Files{0}[Content],
    #"Imported CSV" = Csv.Document(Content,[Delimiter=",", Columns=12, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"CONTRACT_D", type text}, {"PREVIOUS_S", type number}, {"OPEN_PRICE", type number}, {"HIGH_PRICE", type number}, {"LOW_PRICE", type number}, {"CLOSE_PRIC", type number}, {"SETTLEMENT", type number}, {"NET_CHANGE", type number}, {"OI_NO_CON", Int64.Type}, {"TRADED_QUA", Int64.Type}, {"TRD_NO_CON", Int64.Type}, {"TRADED_VAL", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "OI", each [OI_NO_CON]*50),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([CONTRACT_D] = "FUTIDXNIFTY24-NOV-2022" or [CONTRACT_D] = "FUTIDXNIFTY27-OCT-2022" or [CONTRACT_D] = "FUTIDXNIFTY29-DEC-2022"))
in
    #"Filtered Rows"
 
Last edited by a moderator:
Upvote 0
Just as I thought, the UnzipContents function in your code doesn't work as it is supposed to. So it never unzips the zip file content as the current code would expect.

Anyway, here is another approach to unzipping the downloaded file. I used the following approach from lbendin at the Microsoft Power BI Community and adapted it to the remote file download requirement (please note this approach only works when the Zip file contains only one file - that shouldn't be a problem since your zip files look to be containing single file anyway).
community.powerbi.com

Working With Zip Files in Power Query

Using ZIP files directly in Power BI is an attractive idea. In your data source you can store your raw data efficiently – oftentimes the CSV files inside the ZIP are very suitable for compression – and when accessing the ZIP rather than the CSV you can reduce the network traffic as well (let’s...
community.powerbi.com community.powerbi.com

Copy and paste the following code into a new blank query in Power Query, and run it. You'll see it will successfully download and extract the zip file in memory, and then create the tabular data by using the CSV file content. As I also explained before, the data in this CSV file doesn't match the columns you wanted. However, once you have the content of the CSV file, I think it would be pretty easy to manipulate the last query and get the columns you need. Basically, this will be a starting point for you, but most of the hard work is done up to this point.

Power Query: 
let
    fnUnzip = (ZipFile) => let
    
    Url = "https://archives.nseindia.com/content/historical/DERIVATIVES/2022/OCT/fo21OCT2022bhav.csv.zip",

    Source = Binary.Buffer(ZipFile),

    // get the full size of the ZIP file
    Size = Binary.Length(Source),

    //Find the start of the central directory at the sixth to last byte
    Directory = BinaryFormat.Record([ 
                    MiscHeader=BinaryFormat.Binary(Size-6), 
                    Start=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian)
            ]) ,
    Start = Directory(Source)[Start],

    //find the first entry in the directory and get the compressed file size
    FirstDirectoryEntry = BinaryFormat.Record([ 
                    MiscHeader=BinaryFormat.Binary(Start+20), 
                    FileSize=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
                    UnCompressedFileSize=BinaryFormat.Binary(4),
                    FileNameLen=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian),
                    ExtrasLen=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian)
            ]),

    //figure out where the raw data starts            
    Offset = 30+FirstDirectoryEntry(Source)[FileNameLen]+FirstDirectoryEntry(Source)[ExtrasLen],
    Compressed = FirstDirectoryEntry(Source)[FileSize]+1,

    //get the raw data of the compressed file
    Raw = BinaryFormat.Record([
                    Header=BinaryFormat.Binary(Offset), 
                    Data=BinaryFormat.Binary(Compressed)
            ]) 
    // unzip it
in 
    Binary.Decompress(Raw(Source)[Data], Compression.Deflate),
    
    Url = "https://archives.nseindia.com/content/historical/DERIVATIVES/2022/OCT/fo21OCT2022bhav.csv.zip",
    Source = fnUnzip(Web.Contents(Url)),
    ImportedCSV = Csv.Document(Source,[Delimiter=",", Columns=16, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    PromotedHeaders = Table.PromoteHeaders(ImportedCSV, [PromoteAllScalars=true]),
    Result = Table.TransformColumnTypes(PromotedHeaders,{{"INSTRUMENT", type text}, {"SYMBOL", type text}, {"EXPIRY_DT", type date}, {"STRIKE_PR", Int64.Type}, {"OPTION_TYP", type text}, {"OPEN", type number}, {"HIGH", type number}, {"LOW", type number}, {"CLOSE", type number}, {"SETTLE_PR", type number}, {"CONTRACTS", Int64.Type}, {"VAL_INLAKH", type number}, {"OPEN_INT", Int64.Type}, {"CHG_IN_OI", Int64.Type}, {"TIMESTAMP", type date}, {"", type text}})
in
    Result

Here is the result of this query.

power-query.jpg


I intentionally didn't solve the "date" issue above, so you can first see how it is working for the hard-coded date value, then you can try the following.

Create a named range somewhere in your worksheet to keep the required date value, I used reportDate as the cell name. This way we can read the required report date value from the worksheet. Then enter a date into that cell, and this time copy and paste the following code in the Power Query advanced editor:

It is almost the same code that I sent above, however, this one is creating the URL by using the date value that you entered into the cell named as reportDate.

Power Query: 
let
    fnUnzip = (ZipFile) => let
    
    Source = Binary.Buffer(ZipFile),

    // get the full size of the ZIP file
    Size = Binary.Length(Source),

    //Find the start of the central directory at the sixth to last byte
    Directory = BinaryFormat.Record([ 
                    MiscHeader=BinaryFormat.Binary(Size-6), 
                    Start=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian)
            ]) ,
    Start = Directory(Source)[Start],

    //find the first entry in the directory and get the compressed file size
    FirstDirectoryEntry = BinaryFormat.Record([ 
                    MiscHeader=BinaryFormat.Binary(Start+20), 
                    FileSize=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
                    UnCompressedFileSize=BinaryFormat.Binary(4),
                    FileNameLen=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian),
                    ExtrasLen=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian)
            ]),

    //figure out where the raw data starts            
    Offset = 30+FirstDirectoryEntry(Source)[FileNameLen]+FirstDirectoryEntry(Source)[ExtrasLen],
    Compressed = FirstDirectoryEntry(Source)[FileSize]+1,

    //get the raw data of the compressed file
    Raw = BinaryFormat.Record([
                    Header=BinaryFormat.Binary(Offset), 
                    Data=BinaryFormat.Binary(Compressed)
            ]) 
    // unzip it
in 
    Binary.Decompress(Raw(Source)[Data], Compression.Deflate),
    
    // We get the date value from the cell named "reportDate" and construct the Url dynamically
    repDate = Date.From(Excel.CurrentWorkbook(){[Name="reportDate"]}[Content]{0}[Column1]),
    Url =  "https://archives.nseindia.com/content/historical/DERIVATIVES/" & 
        Text.Upper(Date.ToText(repDate, [Format="yyyy/MMM"])) &  
        "/fo" & Text.Upper(Date.ToText(repDate, [Format="ddMMMyyyy"])) & "bhav.csv.zip",

    Source = fnUnzip(Web.Contents(Url)),
    ImportedCSV = Csv.Document(Source,[Delimiter=",", Columns=16, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    PromotedHeaders = Table.PromoteHeaders(ImportedCSV, [PromoteAllScalars=true]),
    Result = Table.TransformColumnTypes(PromotedHeaders,{{"INSTRUMENT", type text}, {"SYMBOL", type text}, {"EXPIRY_DT", type date}, {"STRIKE_PR", Int64.Type}, {"OPTION_TYP", type text}, {"OPEN", type number}, {"HIGH", type number}, {"LOW", type number}, {"CLOSE", type number}, {"SETTLE_PR", type number}, {"CONTRACTS", Int64.Type}, {"VAL_INLAKH", type number}, {"OPEN_INT", Int64.Type}, {"CHG_IN_OI", Int64.Type}, {"TIMESTAMP", type date}, {"", type text}})
in
    Result

Hope this helps.
 
Upvote 0
smozgur said:
Just as I thought, the UnzipContents function in your code doesn't work as it is supposed to. So it never unzips the zip file content as the current code would expect.

Anyway, here is another approach to unzipping the downloaded file. I used the following approach from lbendin at the Microsoft Power BI Community and adapted it to the remote file download requirement (please note this approach only works when the Zip file contains only one file - that shouldn't be a problem since your zip files look to be containing single file anyway).
community.powerbi.com

Working With Zip Files in Power Query

Using ZIP files directly in Power BI is an attractive idea. In your data source you can store your raw data efficiently – oftentimes the CSV files inside the ZIP are very suitable for compression – and when accessing the ZIP rather than the CSV you can reduce the network traffic as well (let’s...
community.powerbi.com community.powerbi.com

Copy and paste the following code into a new blank query in Power Query, and run it. You'll see it will successfully download and extract the zip file in memory, and then create the tabular data by using the CSV file content. As I also explained before, the data in this CSV file doesn't match the columns you wanted. However, once you have the content of the CSV file, I think it would be pretty easy to manipulate the last query and get the columns you need. Basically, this will be a starting point for you, but most of the hard work is done up to this point.

Power Query: 
let
    fnUnzip = (ZipFile) => let
   
    Url = "https://archives.nseindia.com/content/historical/DERIVATIVES/2022/OCT/fo21OCT2022bhav.csv.zip",

    Source = Binary.Buffer(ZipFile),

    // get the full size of the ZIP file
    Size = Binary.Length(Source),

    //Find the start of the central directory at the sixth to last byte
    Directory = BinaryFormat.Record([
                    MiscHeader=BinaryFormat.Binary(Size-6),
                    Start=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian)
            ]) ,
    Start = Directory(Source)[Start],

    //find the first entry in the directory and get the compressed file size
    FirstDirectoryEntry = BinaryFormat.Record([
                    MiscHeader=BinaryFormat.Binary(Start+20),
                    FileSize=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
                    UnCompressedFileSize=BinaryFormat.Binary(4),
                    FileNameLen=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian),
                    ExtrasLen=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian)
            ]),

    //figure out where the raw data starts           
    Offset = 30+FirstDirectoryEntry(Source)[FileNameLen]+FirstDirectoryEntry(Source)[ExtrasLen],
    Compressed = FirstDirectoryEntry(Source)[FileSize]+1,

    //get the raw data of the compressed file
    Raw = BinaryFormat.Record([
                    Header=BinaryFormat.Binary(Offset),
                    Data=BinaryFormat.Binary(Compressed)
            ])
    // unzip it
in
    Binary.Decompress(Raw(Source)[Data], Compression.Deflate),
   
    Url = "https://archives.nseindia.com/content/historical/DERIVATIVES/2022/OCT/fo21OCT2022bhav.csv.zip",
    Source = fnUnzip(Web.Contents(Url)),
    ImportedCSV = Csv.Document(Source,[Delimiter=",", Columns=16, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    PromotedHeaders = Table.PromoteHeaders(ImportedCSV, [PromoteAllScalars=true]),
    Result = Table.TransformColumnTypes(PromotedHeaders,{{"INSTRUMENT", type text}, {"SYMBOL", type text}, {"EXPIRY_DT", type date}, {"STRIKE_PR", Int64.Type}, {"OPTION_TYP", type text}, {"OPEN", type number}, {"HIGH", type number}, {"LOW", type number}, {"CLOSE", type number}, {"SETTLE_PR", type number}, {"CONTRACTS", Int64.Type}, {"VAL_INLAKH", type number}, {"OPEN_INT", Int64.Type}, {"CHG_IN_OI", Int64.Type}, {"TIMESTAMP", type date}, {"", type text}})
in
    Result

Here is the result of this query.

View attachment 76881

I intentionally didn't solve the "date" issue above, so you can first see how it is working for the hard-coded date value, then you can try the following.

Create a named range somewhere in your worksheet to keep the required date value, I used reportDate as the cell name. This way we can read the required report date value from the worksheet. Then enter a date into that cell, and this time copy and paste the following code in the Power Query advanced editor:

It is almost the same code that I sent above, however, this one is creating the URL by using the date value that you entered into the cell named as reportDate.

Power Query: 
let
    fnUnzip = (ZipFile) => let
   
    Source = Binary.Buffer(ZipFile),

    // get the full size of the ZIP file
    Size = Binary.Length(Source),

    //Find the start of the central directory at the sixth to last byte
    Directory = BinaryFormat.Record([
                    MiscHeader=BinaryFormat.Binary(Size-6),
                    Start=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian)
            ]) ,
    Start = Directory(Source)[Start],

    //find the first entry in the directory and get the compressed file size
    FirstDirectoryEntry = BinaryFormat.Record([
                    MiscHeader=BinaryFormat.Binary(Start+20),
                    FileSize=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
                    UnCompressedFileSize=BinaryFormat.Binary(4),
                    FileNameLen=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian),
                    ExtrasLen=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian)
            ]),

    //figure out where the raw data starts           
    Offset = 30+FirstDirectoryEntry(Source)[FileNameLen]+FirstDirectoryEntry(Source)[ExtrasLen],
    Compressed = FirstDirectoryEntry(Source)[FileSize]+1,

    //get the raw data of the compressed file
    Raw = BinaryFormat.Record([
                    Header=BinaryFormat.Binary(Offset),
                    Data=BinaryFormat.Binary(Compressed)
            ])
    // unzip it
in
    Binary.Decompress(Raw(Source)[Data], Compression.Deflate),
   
    // We get the date value from the cell named "reportDate" and construct the Url dynamically
    repDate = Date.From(Excel.CurrentWorkbook(){[Name="reportDate"]}[Content]{0}[Column1]),
    Url =  "https://archives.nseindia.com/content/historical/DERIVATIVES/" &
        Text.Upper(Date.ToText(repDate, [Format="yyyy/MMM"])) & 
        "/fo" & Text.Upper(Date.ToText(repDate, [Format="ddMMMyyyy"])) & "bhav.csv.zip",

    Source = fnUnzip(Web.Contents(Url)),
    ImportedCSV = Csv.Document(Source,[Delimiter=",", Columns=16, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    PromotedHeaders = Table.PromoteHeaders(ImportedCSV, [PromoteAllScalars=true]),
    Result = Table.TransformColumnTypes(PromotedHeaders,{{"INSTRUMENT", type text}, {"SYMBOL", type text}, {"EXPIRY_DT", type date}, {"STRIKE_PR", Int64.Type}, {"OPTION_TYP", type text}, {"OPEN", type number}, {"HIGH", type number}, {"LOW", type number}, {"CLOSE", type number}, {"SETTLE_PR", type number}, {"CONTRACTS", Int64.Type}, {"VAL_INLAKH", type number}, {"OPEN_INT", Int64.Type}, {"CHG_IN_OI", Int64.Type}, {"TIMESTAMP", type date}, {"", type text}})
in
    Result

Hope this helps.
Click to expand...
Thank you very much sir you help me a lot and here the code is working very well in my side.
and sir I am going to put a new thread hope you will help me in that thread also.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
power query code for fetching the data.
2
Replies
15
Views
843
smozgur
smozgur
R
  • Question Question
Power Query Help to Calculate Stock Splits
Replies
0
Views
157
rasinc
R
K
  • Question Question
Power query - stopping refresh if no data available
Replies
2
Views
223
Kariba
K
C
  • Locked
  • Question Question
Refresh Errors in Power BI Desktop loading queries
Replies
1
Views
110
Fluff
Fluff
SimonHughes
  • Solved
How to use a PQ on subsequent downloads
Replies
6
Views
294
SimonHughes
SimonHughes

Forum statistics

Threads
1,223,898
Messages
6,175,272
Members
452,628
Latest member
dd2

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top