1) This technique does not work in VBA, as VBA lacks capability to make HTTP (web) request (you can do it via XMLHttp object of VBA, but no point in adopting such a complex VBA script when you have such easy scripting available) 2) If GSTN is wrong, it will display text #Invalid GSTN#. It will not stop, as GST website internally gives status of invalid GSTN, without raising any error. If you go though my Power Query script, it checks this status number & therefore will not get stuck
The link for vendor filing information is quite different. But yes, I would definitely attempt to try it too & shall release a video if I am able to do it easily
Good video make two table for cookie and browser code in input file itself so that user need not open the code every time. He just need to paste the cookie in one of the row
This idea crossed my mind, but the issue is, it would require few more minutes of demonstration to construct a Table, which may lead to confusion. And many viewers are not even knowing what Power Query is. So, hidden intension is to attract them to learn Power Query & so that they will use latest version of Excel. I will definately cover your approach in sequel of this video which I will be demonstrating it in Power BI. Personally thanks for this suggestion
Not made any such templated sheet, but you can refer my UA-cam live video of matching 2A with books purchase (available on this channel - 2 parts), where I have demonstrated various techniques to track such invoices
Heyy its really awsm....i have been trying to create an excel vba to search gstin... Bt i couldnt make it because of captcha.... Meanwhile i saw this wonderful and a cool techinque... I just tried it.... Bt bt.... While invoking power query, it states the following error - "access to the resource is forbidden".. What should i do about it?
Unfortunately, NSDL does not provide any publicly available API or page for this, or maybe I am not aware of such API. With few pages, even Captch issue is there, which is blocker for this approach.
@@ExcelKida Sir, I have put authorisation code beside gstin in search table before adding column with function used 2 parameters. Changed mcode little. After spending hours, I was able to understand your logic of using gstr1, return and b2b input. Here my doubt can't we use gstin search so that we can capture legal and trade names and address if possible. Any how your code is simple and brilliant. Thanks a lot.
For filing status table there is another URL. But that mechanism will work for one client at a time, as in return you get complete filing table for each vendor. So you will have to build-up Power Query M language script to pick latest return filing value one-by-one for each vendor from their respective tabular data. That is actually complex to build-up If you are a technical & Power Query guy, you need to send below web-request to GST webserver := POST services.gst.gov.in/services/api/search/taxpayerReturnDetails Payload JSON = { gstin: "gstn_of_vendor" } Response = you will get a JSON object containing filing status array, which needs to be converted to table via Power Query
That detail you can easily extract from GST Number directly, 4th digit of PAN. No need of Power Query or any of the approach shown in video. Refer this page, where I have shown it's mechanism erpcloudanalysis.in/utility/gstn-validator
Didn't get your question? This video has nothing to do anything with Tally, it is purely Excel. You can always pull GST number from Tally (ODBC Ledger table) & then wire it up with this Excel template
Annual Return Table 8A already has consolidated 2A of complete financial year which can be downloaded in Excel from GST portal, जो चीज portal पे है उसके लिए आपको tool की क्या जरूरत
very precise and to the point well shared thanks 🎉
This is amazing! Super Like!
Thank you Sir for your videos. Grateful.
Superb Sir
Thanks for the code. It was really helpful.
Amazing 🎉
Great work 👍👍. I tried to get this thru vba but didn't succeed. One query, what if gstin is wrong? Will it stop or jump to next gstin?
1) This technique does not work in VBA, as VBA lacks capability to make HTTP (web) request (you can do it via XMLHttp object of VBA, but no point in adopting such a complex VBA script when you have such easy scripting available)
2) If GSTN is wrong, it will display text #Invalid GSTN#. It will not stop, as GST website internally gives status of invalid GSTN, without raising any error. If you go though my Power Query script, it checks this status number & therefore will not get stuck
Sir Can we get Vendor latest filling status of GSTR-1 and 3B using this technique???
The link for vendor filing information is quite different. But yes, I would definitely attempt to try it too & shall release a video if I am able to do it easily
Good video make two table for cookie and browser code in input file itself so that user need not open the code every time. He just need to paste the cookie in one of the row
This idea crossed my mind, but the issue is, it would require few more minutes of demonstration to construct a Table, which may lead to confusion. And many viewers are not even knowing what Power Query is. So, hidden intension is to attract them to learn Power Query & so that they will use latest version of Excel.
I will definately cover your approach in sequel of this video which I will be demonstrating it in Power BI. Personally thanks for this suggestion
Great job
Can you provide excel sheet for automated reconciliation of GSTR 2B with purchase register
Not made any such templated sheet, but you can refer my UA-cam live video of matching 2A with books purchase (available on this channel - 2 parts), where I have demonstrated various techniques to track such invoices
Nice
Heyy its really awsm....i have been trying to create an excel vba to search gstin... Bt i couldnt make it because of captcha.... Meanwhile i saw this wonderful and a cool techinque... I just tried it.... Bt bt.... While invoking power query, it states the following error - "access to the resource is forbidden".. What should i do about it?
This might come as the cookies are valid for 20-30 min, so you will have to re-generate cookies
Can we get GSTIN Active status for the respective GSTIN ?
This technique supports limited things. We are actually grabbing page output without loading real page
@@ExcelKida Thank you for your response. Kindly let me know when you get to know about pulling the data from GST Database.
GST Database is not directly accessible. You will have to go through API vendors, who sell subscriptions of GST API
In tally there is no option to export group ledger...is their any possibility to download group ledgers in bulk
That feature I will be adding it on my website erpcloudanalysis.in utility section, as even I require this badly
Sir I have some GSTIN and Trade name which is repeating also. In pivot table how can I manage trade name against GSTIN
You need to concatenate both GSTN & Trade Name by separator like dash, and then drag that column into Pivot
By using the same technique can I pull the PAN No from traces?
Unfortunately, NSDL does not provide any publicly available API or page for this, or maybe I am not aware of such API. With few pages, even Captch issue is there, which is blocker for this approach.
hi, auth token is not showing, How to view it in request headers section in cookies
Sir, Can we put cookies in Excel cells to be read by mcode, instead of editing code every time.
Yes you can
I will plan a video for this for sure
@@ExcelKida Sir, I have put authorisation code beside gstin in search table before adding column with function used 2 parameters. Changed mcode little. After spending hours, I was able to understand your logic of using gstr1, return and b2b input. Here my doubt can't we use gstin search so that we can capture legal and trade names and address if possible. Any how your code is simple and brilliant. Thanks a lot.
can we also load address by gst number ?
It requires one more additional step since it's hyperlink is different. Will plan a video for that too
how to GST NO against supplier name written... I have file where supplier name is already written and I want to get GST NO automatically ..
Reverse is not possible. That facility is with GST Suvidha Provider, which you can buy via API plans
Can I get filling status of of that gstin in these manner
For filing status table there is another URL. But that mechanism will work for one client at a time, as in return you get complete filing table for each vendor. So you will have to build-up Power Query M language script to pick latest return filing value one-by-one for each vendor from their respective tabular data. That is actually complex to build-up
If you are a technical & Power Query guy, you need to send below web-request to GST webserver :=
POST services.gst.gov.in/services/api/search/taxpayerReturnDetails
Payload JSON = { gstin: "gstn_of_vendor" }
Response = you will get a JSON object containing filing status array, which needs to be converted to table via Power Query
Can we get taxpayer type for each gstin ? If s, please help sir
That detail you can easily extract from GST Number directly, 4th digit of PAN. No need of Power Query or any of the approach shown in video. Refer this page, where I have shown it's mechanism
erpcloudanalysis.in/utility/gstn-validator
@@ExcelKida I want like Regular or SEZ for each gstin.
I will have to check, if those details are being returned back by URL or not
CAN WE FETCH COMPANY NAME THROUG POWER QUERY TO EXCEL FROM TALLY?
PROVIDE CODE SIR
Didn't get your question?
This video has nothing to do anything with Tally, it is purely Excel.
You can always pull GST number from Tally (ODBC Ledger table) & then wire it up with this Excel template
it says data source error when i try to invoke the function, could you help me with that sir?
Can you share me screenshot of exact error?
@@ExcelKida sir is there any other way to contact you?
My email & mobile number are always shared at the end of every video.
You can email/WhatsApp me the screenshot of error
Sir,
Can update principal address
I don't remember if Principal Address information is returned back in that API. It is too limited thing I would say.
HOW TO MAKE VENDOR NAME VISIBLE BY ENTERING GST NUMBER ON THE A2 CELL AND GETTING IT ON B2
I guess thats what video is about. You need to watch video till end to know this trick
Sir ek tool banaye jo Poore saal ka GSTR 2A ko download kar sake..excel sheet me
Because git*** me saare tool bekar ho gye...pls
Annual Return Table 8A already has consolidated 2A of complete financial year which can be downloaded in Excel from GST portal, जो चीज portal पे है उसके लिए आपको tool की क्या जरूरत
Thanks sir for replies...
Helo
this technique is not working
Sometimes your cookie might die. Try refreshing cookie
@@ExcelKida will be reply after try again