How to Load NSE ZIP and CSV Files into Excel with One Date Parameter
Question details
The user needs to retrieve financial datasets from an NSE ZIP file and an NSE CSV file via date-based URLs, and load both into separate Excel worksheets using one centralized date input.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Automating the retrieval of daily web-based financial data from multiple URL structures (ZIP and CSV) without manually editing the date for each query.
- Observed behavior
- Requires a Power Query setup where a single cell dictates the date parameter for two distinct web queries, which then output to independent worksheets.
Ensure you are using an Excel version that supports Power Query (Excel 2016 or newer) and verify the exact date formatting required by the NSE ZIP and CSV URLs.
Create a Shared Date Parameter and Build Separate Queries
Use Power Query to create a centralized date variable, append it to the specific web URLs in the Advanced Editor, and load the respective outputs to different sheets.
Power Query allows you to convert an Excel cell value into a reusable parameter. By formatting the date string appropriately for each target URL, you can fetch both datasets simultaneously.
You can optionally use a short VBA macro to trigger a refresh of all data connections whenever the parameter cell is modified.
Enter your desired date in an Excel cell, turn it into a Table (Ctrl+T), and go to Data > From Table/Range. In the Power Query Editor, right-click the date value and select 'Drill Down' to create a dynamic text or date parameter.
Go to Data > From Web and enter a placeholder URL. Open the Advanced Editor (Home > Advanced Editor) and modify the M code to concatenate your new date parameter into the Web.Contents URL string.
Create a second Web Query for the ZIP URL. In the Advanced Editor, insert the date parameter into the URL structure. Add custom M code steps to extract and decompress the binary contents of the ZIP file.
For both queries, click 'Close & Load To...'. Select 'Table' and choose different existing or new worksheets to ensure the datasets remain separate.
To update your data, simply change the date in your parameter table and click 'Refresh All' on the Data tab. Optionally, attach a VBA Worksheet_Change event to this cell to automate the refresh process.
Discover WPS Office for Your Daily Data Analysis Needs
While advanced M-code Power Query workflows for binary ZIP extraction are highly specific to Microsoft Excel, WPS Office provides a lightweight, free alternative for comprehensive standard spreadsheet management, data processing, and VBA macro-based automation.
- 1. Download the WPS Office Suite: Visit the official WPS website to download the lightweight installer for free.
- 2. Install and Launch: Follow the simple installation prompts and open WPS Spreadsheet.
- 3. Open Your Spreadsheets: Open your existing Excel workbooks seamlessly to continue working on your data without losing formatting.
Frequently Asked Questions
How do I pass an Excel cell value into a Power Query URL?
You can convert the cell containing your value into an Excel Table, load it into Power Query via 'From Table/Range', right-click the specific cell value, and select 'Drill Down'. You can then reference this query's name as a variable inside the Web.Contents function in your Advanced Editor.
Can Power Query extract CSV files directly from a downloaded ZIP folder?
Yes. You can use the Web.Contents function to fetch the ZIP file. Because Power Query reads it as binary data, you will need to apply custom M code or use third-party custom functions designed to decompress the binary structure and read the underlying CSV contents.
How do I ensure queries load to different worksheets instead of the same one?
When you are ready to load your data in the Power Query Editor, click the drop-down arrow under 'Close & Load' and choose 'Close & Load To...'. This will prompt a dialog box where you can specify the exact worksheet location for each individual query.
