March 16, 2015 at 16:10Unloading the conditions of public procurement tenders with environmental protection zakupki.gov.ru
Notes:
Quote:
For many commercial and other companies, the "public procurement" system is the main way to attract state (budget) funds into economic activity. Information on the procurement of goods, services, research work carried out by companies with state participation (and other individual types of companies) without fail (according to federal laws No. 223, 94, 44) is posted on the government procurement website. Commercial companies also often publish their tenders on the official website of public procurement.
Links to the mentioned laws, information on the ConsultantPlus website:
Thus, the information on tenders published on the government procurement website is the central source of relevant information on possible “state” orders by profile for many companies from various fields of activity - from security services to geophysical surveys. Therefore, the need to have regularly updated information about ongoing competitions that fall under certain criteria arises from each organization participating in public procurement. In this article, we consider various ways and a practical example of the implementation of this need.
Most often, the task is set by the management in front of the marketing or IT department of the company in approximately the following wording: “a daily summary of public procurement competitions that meet the following criteria is needed ...” . It is daily, because sometimes between the publication of tender documentation and the end of the application for it, a period of less than 10 days is declared, on average - about two weeks. There is not much time for collecting all the necessary documentation for participating in the competition, every day is “on the count”.
We list the options for implementing the task.
The site itself http://zakupki.gov.ru/ provides the ability to customize search and selection of contests by parameters.
Disadvantages:
To partially automate the process, I once wrote a plug-in for the FireFox browser, which was supposed to do the daily download of selected contests.
In a nutshell, the plugin generated the address in the form of something like this line:
Naturally, for 94 and 223 of the law, the request parameters are different. As you can see, OKPD, price range, etc. you have to “sew up” into the request, because otherwise the number of contests found will be too large and the search results can only be partially downloaded.
In response to a request from the market, several companies organized search sites for information about tenders. Often, such sites provide not only information about public procurement, but also about “private” tenders from electronic sites.
Several such systems:
Other.
I will separately mention the state expenditures about which there was an article on Habré .
The purpose of this article is not to compare or analyze these resources, so neither their advantages nor disadvantages are given here.
In some (I know a few) cases, the management of a company participating in tenders poses very specific tasks regarding the parameters of selecting information, the regularity of the search or the design of the search result. In such cases, they turn to IT specialists, “manual work” on monitoring and selecting contests becomes too time-consuming.
There are examples of ordering the described work from freelancers. For example, on SQL.ru , on Weblancer . Finally, you can purchase a turnkey solution from "time-tested" artists . However, the main part of this article describes the procedure for the independent execution of a task.
The main necessary knowledge about the data source: OOS (the all-Russian official website for public procurement) has a public ftp server . At the same time, if http is regularly (usually at night and on holidays) not available due to “maintenance”, then ftp works (according to my practice) quite reliably.
The addresses of ftp servers are divided according to federal laws applicable for tendering:
Federal Law No. 223: ftp://ftp.zakupki.gov.ru/out/
Federal Law No. 94 and Federal Law No. 44: ftp://zakupki.gov.ru
The directory structure for 223 laws is completely transparent. As for 94 and 44, the following subdirectories of interest to us are located here:
The rest of the catalogs contain regulatory and reference information, information on bank guarantees, unloading according to the rules and within the framework of solving the problem are not of interest.
Further, the necessary excerpts from the explanations on the procedure for uploading information about published documents in the region to the FTP server of the All-Russian official site, set of quotes:
We note that in our view is only interested in the notification ( notice ). All other types of documents are not used as part of the task!
The current version of the above information can be downloaded from the OOS in the form of documents "Information Exchange Schemes ...".
To request the date range in which contests should be downloaded, I made a userForm using the MonthView element . Its settings allow you to expand two months side by side (suppose the range is no longer than a month), show the current date (red box), set the selected date range by default.
The range from February 26 to March 4 is selected as an example. The selected dates are read from the properties of the Form:
This filter allows you to select files (the name structure of which we know) in a given date range:
Pay attention to the fact that here the semicolon separates the alternative options, so the specified filter is suitable for files generated by all three laws.
I download the files to the specified directory, which I cleaned up from old downloads using FSO methods:
Next, select the target folder bFld = FSO.GetFolder (...) and destroy the subdirectories:
The use of the program turned out to be completely optional, but extremely successful.
The second parameter is asynchronous execution.
We start the download. Make sure the destination folder exists using
Create shell object
and apply the main "feature" of the described approach - the namespace method :
The specified line implements a call to FTP, returning folders and files. Note that the parameter passed must be of type Variant , not String .
You can select all directories as follows:
Select subdirectories and files ( 96 = 32 + 64 ) and apply a filter like the one that was built at the beginning of the paragraph to select files by date - like this:
It remains to specify the target (local) folder for downloading in the same way:
And start “copying” (it is also “downloading”) as follows:
This command starts an external process (Windows displays a standard progress bar for copying files); its execution from vba is not directly controlled. However, we need to wait until its completion, for which we use the following method in the loop to check for the presence of the last of the copied files:
So arranged "waiting" lasts exactly as long as the files are copied.
After going through all the directories and subdirectories and downloading all the filtered files, we proceed to their processing on the local machine:
To do this, we again use the namespace method .
We sort through all the archives in the folder
and take advantage of the fact that these archives in Windows are visible as subdirectories! Accordingly, the entire contents of the archive are available like this:
(Again, I draw attention to the fact that the parameter must be Variant , which is why type conversion from a string is necessary).
And again, the same CopyHere command allows you to "copy" (actually extract) from the archive all the files to the target folder:
and asynchronous execution makes us in a loop wait for the completion of this command in the same manner as described above.
Among the unzipped files there is a lot of “garbage”. For example, in a certain region there were no purchases on a certain day, the OOS generates an empty file for this date. Therefore, before parsing xml, I prefer to remove the excess. Using FSO iterate over files
“Screening” is easily done by the size of the file (fl.Size <= 198) and filtering its name Not (LCase (fl.Name) Like "* noti *")) .
Deleting a file is extremely simple: fl.Delete
The decoding of xml depends on their scheme, which sometimes changes on the OOS. Therefore, the following are the basic techniques, without focusing on individual fields and data. We start, of course, with
In this part, in addition to FSO, we will need XML for working with files:
And, of course, the ActiveSheet in the Excel workbook where we will write the information.
Let's start by stopping Excel rendering for a while so that it doesn't flicker:
The fundamental point is that
This is how the namespace definitions for parsing xml formed in 94 Federal Laws look. For 44, it will be a little different:
Actually, all the given data is taken from the header of any downloaded xml file, only q is added for the namespace by default .
Next, the fields (more precisely, the xpath pointing to them) are determined that need to be downloaded. Currently, for 94 Federal Laws they look something like this: ".//oos:notificationNumber", ".//oos:lot", ".//oos:orderName", ".//oos:maxPrice". For 44 Federal Laws, there is another structure (thanks to the OOS programmers): ".//q:purchaseNumber", ".//q:lot", ".//q:purchaseObjectInfo", ".//q:maxPrice|.// q: price | .// q: totalSum " .
We see that there is no ambiguity, you have to configure parsing not only relying on published schemes,
Check if the xml-file is “readable” by a double condition:
If the file was read (so far I haven’t had any problems with the files downloaded from the OOS), you can actually parse its contents. First of all, I recommend reading the field that describes the composition of the message ( notice ).
And to check that the contents of the file is precisely the announcement of the competition, and not its cancellation, notification of publication of the protocol, etc. like that:
Since in the xml generated by 223 Federal Laws, the name of the document type is “hidden”, you can add:
Next is the actual writing of data from xml to sheet cells:
Etc.
If XPath can provide several options (indicating, for example, the names of lots) and we want to keep all of them, this construction will help:
Some fields in the document may be absent, then we omit them by the condition:
Downloading files with ftp can be implemented in various ways. I have described one using shell.namespace that works and is VERY easy to implement.
After downloading, automatically ranking (selecting) and formatting the list of tenders, my daily selection of tenders (for all Federal Laws) looks something like this:
The approach described above allows you to download data on tenders, contracts, procurement plans, etc. with OOS, because all this information is published on open ftp . I don’t give all the code of the program and I can’t give it, since it is “intellectual property”. However, anyone who knows the basics of vba and, more importantly, patience can restore the program from the given key sections of code .
Patience is needed, firstly, when parsing files and subdirectories on ftp: you must not download too much and do not miss the right one. And secondly, when parsing xml. However, there is already a question of the task: which fields, in what order, how formatted, the customer wants to see.
Good luck and victories to everyone: - in competitions - and personal ones!
- OOS - the all-Russian official website ( Government procurement ).
- Required knowledge for understanding the article: VBA (MS Excel).
Introduction
Quote:
“The official website of the Russian Federation on the Internet for placing information on placing orders for the supply of goods, works, services ... is designed to provide free and gratuitous access to complete and reliable information about the contract system in the field of procurement and procurement of goods, works, services, certain types of legal entities, as well as for the formation, processing and storage of such information. ”
For many commercial and other companies, the "public procurement" system is the main way to attract state (budget) funds into economic activity. Information on the procurement of goods, services, research work carried out by companies with state participation (and other individual types of companies) without fail (according to federal laws No. 223, 94, 44) is posted on the government procurement website. Commercial companies also often publish their tenders on the official website of public procurement.
Links to the mentioned laws, information on the ConsultantPlus website:
- Federal Law of July 21, 2005 N 94-ФЗ (as amended on July 2, 2013) "On placing orders for the supply of goods, performance of work, and the provision of services for state and municipal needs"
- Federal Law of July 18, 2011 N 223-ФЗ (as amended on March 12, 2014, as amended on December 29, 2014) "On the procurement of goods, works, services by certain types of legal entities" (as amended and additional, entered into force from 01.01.2015)
- Federal Law dated 05.04.2013 N 44-ФЗ (as amended on 08.03.2015) "On the Contract System in the Sphere of Procurement of Goods, Work, Services for State and Municipal Needs"
Thus, the information on tenders published on the government procurement website is the central source of relevant information on possible “state” orders by profile for many companies from various fields of activity - from security services to geophysical surveys. Therefore, the need to have regularly updated information about ongoing competitions that fall under certain criteria arises from each organization participating in public procurement. In this article, we consider various ways and a practical example of the implementation of this need.
Options for obtaining public procurement information
Most often, the task is set by the management in front of the marketing or IT department of the company in approximately the following wording: “a daily summary of public procurement competitions that meet the following criteria is needed ...” . It is daily, because sometimes between the publication of tender documentation and the end of the application for it, a period of less than 10 days is declared, on average - about two weeks. There is not much time for collecting all the necessary documentation for participating in the competition, every day is “on the count”.
We list the options for implementing the task.
1. OOS website (public procurement)
The site itself http://zakupki.gov.ru/ provides the ability to customize search and selection of contests by parameters.
Disadvantages:
- The search does not work correctly, sometimes the contests are “not found”. I am a witness to a long slave precisely through the official website, I have verified the statement;
- The site often does not work at night and on holidays;
- Limit in the number of results. If the search yields more than 500 lines, downloading the contest data (in the form of a .csv format table) using the site’s resources is not possible.
To partially automate the process, I once wrote a plug-in for the FireFox browser, which was supposed to do the daily download of selected contests.
In a nutshell, the plugin generated the address in the form of something like this line:
"http://zakupki.gov.ru/epz/order/extendedsearch/search.html?"
+ "placeOfSearch=FZ_44"
+ "&orderPriceFrom=" + priceArray[i] + "&orderPriceTo=" + (priceArray[i+1]-1)
+ "&orderPriceCurrencyId=-1"
+ "&orderPublishDateFrom=" + OrderDate + "&orderPublishDateTo=" + OrderDate
+ "&headAgencyWithSubElements=true&matchingWordPlace44=NOTIFICATIONS"
+ "&law44.okpd.withSubElements=true”
+ "&law44.okpd.ids=31301%2C37097%2C50876%2C51122" + "&law44.advantages[MP44]=I&law44.advantages[UG44]=I"
+ "&law44.advantages[IN44]=I&law44.advantages[MPSP44]=I"
+ "&morphology=false&strictEqual=false";
Naturally, for 94 and 223 of the law, the request parameters are different. As you can see, OKPD, price range, etc. you have to “sew up” into the request, because otherwise the number of contests found will be too large and the search results can only be partially downloaded.
2. Specialized web - competition search services
In response to a request from the market, several companies organized search sites for information about tenders. Often, such sites provide not only information about public procurement, but also about “private” tenders from electronic sites.
Several such systems:
- Multitender.ru : “a free specialized search engine that allows you to track government procurements and commercial tenders”;
- TenderCAT : “The TenderCAT catalog is designed to facilitate the search for state and commercial tenders and auctions in a huge mass of orders published daily on the government procurement portal (http://zakupki.gov.ru) in all regions and areas of activity”;
- East Budget : “The website of public procurements and tenders - the best search and analytics from the East Budget”;
- Purchasing 360
Other.
I will separately mention the state expenditures about which there was an article on Habré .
The purpose of this article is not to compare or analyze these resources, so neither their advantages nor disadvantages are given here.
3. Self-development for downloading contests.
In some (I know a few) cases, the management of a company participating in tenders poses very specific tasks regarding the parameters of selecting information, the regularity of the search or the design of the search result. In such cases, they turn to IT specialists, “manual work” on monitoring and selecting contests becomes too time-consuming.
There are examples of ordering the described work from freelancers. For example, on SQL.ru , on Weblancer . Finally, you can purchase a turnkey solution from "time-tested" artists . However, the main part of this article describes the procedure for the independent execution of a task.
Downloading competition data from http://zakupki.gov.ru using Excel VBA
Initial data
The main necessary knowledge about the data source: OOS (the all-Russian official website for public procurement) has a public ftp server . At the same time, if http is regularly (usually at night and on holidays) not available due to “maintenance”, then ftp works (according to my practice) quite reliably.
The addresses of ftp servers are divided according to federal laws applicable for tendering:
Federal Law No. 223: ftp://ftp.zakupki.gov.ru/out/
Login and password:
fz223free
Federal Law No. 94 and Federal Law No. 44: ftp://zakupki.gov.ru
Login and password:
free
The directory structure for 223 laws is completely transparent. As for 94 and 44, the following subdirectories of interest to us are located here:
- 94fz catalog containing data of public unloading in accordance with 94FZ (other catalogs - 44FZ),
- fcs_regions directory containing data on the complete regional upload of information published on the environmental protection system in accordance with 94FZ.
The rest of the catalogs contain regulatory and reference information, information on bank guarantees, unloading according to the rules and within the framework of solving the problem are not of interest.
Further, the necessary excerpts from the explanations on the procedure for uploading information about published documents in the region to the FTP server of the All-Russian official site, set of quotes:
Full regional unloading includes all documents of the following types published on the CAB:
• published notices;
• published changes to notices;
• published protocols;
• published contract information;
• published contract changes;
• published information on the performance / termination of contracts.
We note that in our view is only interested in the notification ( notice ). All other types of documents are not used as part of the task!
Unloading is carried out in archived files of the XML format.
A single file can contain documents of only one type in an amount not exceeding 3,000 entries. If the number of documents to be uploaded exceeds 3,000 entries, the system generates several files and puts each in a separate archive.
All generated and archived XML files are uploaded to an FTP server ... the files in the upload are divided into directories corresponding to the region. In each directory of the region there are 3 more directories: notifications, protocols and contracts. Each of the notifications, protocols and contracts directories also has a daily directory.
Published documents are uploaded to the FTP server in the following order:
·Each calendar day (daily) uploads a list of documents published for the previous calendar day. At the same time ... the uploading of notices by region is done in the <Region Name> / notifications / daily;
· Each calendar month (monthly) uploads a list of documents published for the previous calendar month. At the same time ... the uploading of notices by region is done in the <Region Name> / notifications directory;
In the daily and monthly downloads, all types of documents published for the past calendar day or calendar month, respectively, are always unloaded.
If at the time of uploading for the past period there was not a single published document of any type, then the XML file with this type of document is unloaded empty.
After completing the monthly uploads, directories with daily uploads for the past month are cleared.
The names of the regional upload files have the following structure:
<document-type_region_start_period_end_period_number.xml.zip>,
where:
• document type - takes the value notification, protocol or contract for notifications, protocols, and contract information, respectively;
• region - name of the unloading region;
• start-period - the start date of the period for selecting documents by the date-time of publication of the uploaded documents in the format yyyyddmm_hhmmss, where yyyy - year, mm - month (number), dd - day, hh - hour, mm - minutes, ss - seconds;
• end-period - the end date of the period for selecting documents according to the publication date and time of the uploaded documents in the format yyyyddmm_hhmmss, where yyyy - year, mm - month (number), dd - day, hh - hour, mm - minutes, ss - seconds;
• number - serial number of the generated file;
The current version of the above information can be downloaded from the OOS in the form of documents "Information Exchange Schemes ...".
Description of the program for automatically downloading competitive information (VBA MS Excel, Windows).
Training
- We connect scripting Microsoft Scripting Runtime. For FSO functionality ( \ Windows \ System32 \ scrrun.dll )
- We include scripting of Microsoft XML, v.6
To request the date range in which contests should be downloaded, I made a userForm using the MonthView element . Its settings allow you to expand two months side by side (suppose the range is no longer than a month), show the current date (red box), set the selected date range by default.
The range from February 26 to March 4 is selected as an example. The selected dates are read from the properties of the Form:
MonthView.SelStart и MonthView.SelEnd This filter allows you to select files (the name structure of which we know) in a given date range:
fltr = "*_" & Format(targetDate, "yyyymmdd") & "*" & Format(targetDate + 1, "yyyymmdd") & "*.zip;" & "*_" & Format(targetDate, "yyyymmdd") & "*" & Format(targetDate, "yyyymmdd") & "*.zip"
Pay attention to the fact that here the semicolon separates the alternative options, so the specified filter is suitable for files generated by all three laws.
I download the files to the specified directory, which I cleaned up from old downloads using FSO methods:
Dim FSO As FileSystemObject
Set FSO = New FileSystemObject
Next, select the target folder bFld = FSO.GetFolder (...) and destroy the subdirectories:
For Each SubFolder In bFld.SubFolders
SubFolder.Delete
Next
The use of the program turned out to be completely optional, but extremely successful.
Application.Speech.Speak Application.Speech.Speak "Downloading purchase notices", True The second parameter is asynchronous execution.
Download files
We start the download. Make sure the destination folder exists using
FSO.FolderExists
Create shell object
Set myShell = CreateObject("Shell.Application")
and apply the main "feature" of the described approach - the namespace method :
Set ftpItems = myShell.Namespace(адресFTP).Items
The specified line implements a call to FTP, returning folders and files. Note that the parameter passed must be of type Variant , not String .
You can select all directories as follows:
ftpItems.Filter 32, "*".
Select subdirectories and files ( 96 = 32 + 64 ) and apply a filter like the one that was built at the beginning of the paragraph to select files by date - like this:
ftpItems.Filter 96, fltr.
It remains to specify the target (local) folder for downloading in the same way:
set tFolder = myShell.Namespace(tgtFolder)
And start “copying” (it is also “downloading”) as follows:
tFolder.CopyHere ftpItems, 20
This command starts an external process (Windows displays a standard progress bar for copying files); its execution from vba is not directly controlled. However, we need to wait until its completion, for which we use the following method in the loop to check for the presence of the last of the copied files:
While Len(Dir$(tgtFolder & "\" & ftpItems.Item(ftpItems.Count - 1).Name)) = 0
Sleep 1: DoEvents
Wend
So arranged "waiting" lasts exactly as long as the files are copied.
Unpacking (unzipping) downloaded files
After going through all the directories and subdirectories and downloading all the filtered files, we proceed to their processing on the local machine:
Application.Speech.Speak "Unzipping archives", True.
To do this, we again use the namespace method .
We sort through all the archives in the folder
For Each fl In tFolderItems
and take advantage of the fact that these archives in Windows are visible as subdirectories! Accordingly, the entire contents of the archive are available like this:
Set flItems = myShell.Namespace(CVar(tgtFolder & "\" & fl.Name)).Items
(Again, I draw attention to the fact that the parameter must be Variant , which is why type conversion from a string is necessary).
And again, the same CopyHere command allows you to "copy" (actually extract) from the archive all the files to the target folder:
myShell.Namespace(tgtFolder).CopyHere flItems, 20
and asynchronous execution makes us in a loop wait for the completion of this command in the same manner as described above.
Among the unzipped files there is a lot of “garbage”. For example, in a certain region there were no purchases on a certain day, the OOS generates an empty file for this date. Therefore, before parsing xml, I prefer to remove the excess. Using FSO iterate over files
Set fold = FSO.GetFolder(tgtFolder)
For Each fl In fold.Files
“Screening” is easily done by the size of the file (fl.Size <= 198) and filtering its name Not (LCase (fl.Name) Like "* noti *")) .
Deleting a file is extremely simple: fl.Delete
Decoding XML with Competition Data
The decoding of xml depends on their scheme, which sometimes changes on the OOS. Therefore, the following are the basic techniques, without focusing on individual fields and data. We start, of course, with
Application.Speech.Speak "Decoding files", True.
In this part, in addition to FSO, we will need XML for working with files:
Dim xml As MSXML2.DOMDocument60
Set xml = New DOMDocument60: xml.async = False: xml.validateOnParse = True
And, of course, the ActiveSheet in the Excel workbook where we will write the information.
Let's start by stopping Excel rendering for a while so that it doesn't flicker:
Application.ScreenUpdating = False
The fundamental point is that
“XPath treats an empty prefix as a null namespace. In other words, only prefixes mapped to namespaces can be used in XPath queries. This means that if you need to build a query for a namespace in an XML document, then even if it is the default namespace, you must define a prefix for it. ”Therefore, to successfully parse the fields of downloaded documents for the namespace, we add a prefix by default. For example, "q":
xml.setProperty "SelectionNamespaces", " xmlns:q= 'http://zakupki.gov.ru/oos/export/1'
xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'
xmlns:oos='http://zakupki.gov.ru/oos/types/1'"
This is how the namespace definitions for parsing xml formed in 94 Federal Laws look. For 44, it will be a little different:
xml.setProperty "SelectionNamespaces", "xmlns:q='http://zakupki.gov.ru/oos/types/1'
xmlns:ns2='http://zakupki.gov.ru/oos/export/1'
xmlns:ns3='http://zakupki.gov.ru/oos/printform/1'"
Actually, all the given data is taken from the header of any downloaded xml file, only q is added for the namespace by default .
Next, the fields (more precisely, the xpath pointing to them) are determined that need to be downloaded. Currently, for 94 Federal Laws they look something like this: ".//oos:notificationNumber", ".//oos:lot", ".//oos:orderName", ".//oos:maxPrice". For 44 Federal Laws, there is another structure (thanks to the OOS programmers): ".//q:purchaseNumber", ".//q:lot", ".//q:purchaseObjectInfo", ".//q:maxPrice|.// q: price | .// q: totalSum " .
We see that there is no ambiguity, you have to configure parsing not only relying on published schemes,
Check if the xml-file is “readable” by a double condition:
If Not xml.Load(tgtFolder & "\" & fl.Name) then…If (xml.parseError.ErrorCode <> 0) then…
If the file was read (so far I haven’t had any problems with the files downloaded from the OOS), you can actually parse its contents. First of all, I recommend reading the field that describes the composition of the message ( notice ).
purchaseType = LCase(xml.DocumentElement.ChildNodes(0).BaseName)
documentType = LCase(xml.DocumentElement.BaseName)
And to check that the contents of the file is precisely the announcement of the competition, and not its cancellation, notification of publication of the protocol, etc. like that:
If Not (purchaseType Like "*cancel*" Or purchaseType Like "*protocol*" Or documentType Like "*cancel*") Then
Since in the xml generated by 223 Federal Laws, the name of the document type is “hidden”, you can add:
If (purchaseType Like "*notification*" Or documentType Like "*notice*") Then
Next is the actual writing of data from xml to sheet cells:
Range("A" & i) = xml.DocumentElement.SelectSingleNode(строкаXPathсоотвПоля).Text
Etc.
If XPath can provide several options (indicating, for example, the names of lots) and we want to keep all of them, this construction will help:
For Each it In lot.SelectNodes(строкаXPathсоотвПолей)
Range("E" & i) = Range("E" & i) & it.Text & "; "
Next
Some fields in the document may be absent, then we omit them by the condition:
If Not xml.DocumentElement.SelectSingleNode(строкаXPathсоотвПоля) Is Nothing Then
Conclusion
Downloading files with ftp can be implemented in various ways. I have described one using shell.namespace that works and is VERY easy to implement.
After downloading, automatically ranking (selecting) and formatting the list of tenders, my daily selection of tenders (for all Federal Laws) looks something like this:
The approach described above allows you to download data on tenders, contracts, procurement plans, etc. with OOS, because all this information is published on open ftp . I don’t give all the code of the program and I can’t give it, since it is “intellectual property”. However, anyone who knows the basics of vba and, more importantly, patience can restore the program from the given key sections of code .
Patience is needed, firstly, when parsing files and subdirectories on ftp: you must not download too much and do not miss the right one. And secondly, when parsing xml. However, there is already a question of the task: which fields, in what order, how formatted, the customer wants to see.
Good luck and victories to everyone: - in competitions - and personal ones!