May 28, 2019 at 15:49How to automate public procurement monitoring for free
- Tutorial
I will say right away, the article is more technical, this is an instruction.
But I'll start with the story.
Now I work for a company with 100 percent state participation, the founder is one respected state corporation.
A year ago, I launched a new process here - participation in public procurement. Yes - yes, that’s how it happens, the state company did not participate in government procurements.
Attempts to launch it were, but unsuccessful.
When I came to work in this company, someone monitored purchases, but the sales department did not receive tenders from this someone, although they sent them keywords on the subject of the department. The process was broken.
I was at a loss for a while, until I decided to restart this process, asking me to pay for an account on Kontur.
I set everything up and began to monitor it myself. I found contests, gave it to the sales department, spun, spun.
People like when something happens. Sales department caught fire.
If you briefly describe what you need to start the process of participation in public procurement, then you need to:
1. Set up monitoring of public procurement
2. So that someone selects projects, understanding the Federal Law and reading all the requirements. Remove excess.
3. Keep records of the status of projects in a single interface.
4. Prepare an application for participation in the competition, understanding the structure of your price
5. Have accounts on electronic trading floors (ETP)
6. Be able to write complaints to the FAS on demolition of tenders (lawyer)
7. Analyze your market, I’m sorry
My account in Kontur helps me a lot. Purchases do p.p. 1-3 and 7. I already wrote earlier how to get data from Kontur. Purchasing at a minimum tariff .
But the account with them and similar services is paid. And not everyone can afford the acquisition and sit, tormented.
Here for you, my future millionaires, I sketched instructions on how to make the selection of contests more convenient and completely free.
As a result, we get just such a tablet, the data in which will be updated independently:
Let's go!
Come on zakupki.gov.ru/epz/order/extendedsearch/search.html
It is important from extendedsearch, because the results produced by quicksearch differ from the extended ones. In particular, in quicksearch the system does not substitute the name of the customer, but we want to display it on the tablet. We use extended.
So, in the search form, enter:
- the search words to search for the contest. In my case, this is “fare payment”
- the procurement phase. We are interested in "Submitting an Application", the rest are disabled.
Click "Find."
The system will display the search results. If this is what you need, we get an RSS feed with updates for this request by clicking on the RSS icon.
Copy yourself the URL of the RSS feed [in our case - zakupki.gov.ru/tinyurl/ab14226a-5b2a-4de6-b283-112b6972f6bc ]
Go to IFTTT .
If you are not registered, then do it and go to “My Applets”.
Press the “New Applet” button to create a new applet (trigger and action).
On the next screen, click on the "+ this" part, the system will prompt you to select a service. In the search bar, type “rss” or find the “RSS Feed” trigger in the service feed.
Click on the “RSS Feed” square.
2 options will appear, select “New feed item”.
The trigger will work if a new object appears in the RSS feed
On the trigger creation screen, insert the URL of our RSS feed and click "Create trigger".
Next, click the "+ that" button.
On the action search screen, write “sheets” and click on the found action.
On the next screen, select the option “Add row to spreadsheet” (add a row to the table).
Configure IFTT how to fill in the table.
Spreadsheet name - the name of the table file. If such a table does not exist, then IFTTT will create it on your disk. You can see the table in your list of Google tables .
Formatted row - the rule for filling the line. We select the fields we need, separating them with the ||| sign.
Fill in
{{EntryPublished}} ||| {{EntryTitle}} ||| {{EntryUrl}} ||| {{EntryContent}} ||| zakupki.gov.ru {{FeedUrl}} ||| {{EntryAuthor}}
That is, the following will be written in the cells:
DATE ||| COMPETITION NAME ||| Record URL ||| COMPETITION DESCRIPTION ||| RSS feed ||| CUSTOMER NAME
Drive folder path - the name of the folder on your Google Drive , in which the file with the table will be placed. I put all the tables in the zakupki folder.
Click "Create action".
On the final screen, select the name for the IFTTT rule.
And click Finish.
Everything, the rule has been created, the table will begin to fill up as soon as new contests appear in the RSS feed.
Paste the headings above the columns. Here's what we see: It
remains to select from the description the name of the competition and the initial price.
Add the column “Competition Name” and insert the following formula in the first (after the headings) cell:
The formula will parse the column D (description) and automatically fill in the name of the contest in this column when a new line appears in the table.
Add a column (enter the name of the column "Starting Price") and insert the following formula in the first (after the headings) cell:
And the final touch. It happens that a link comes in two forms. With and without a domain:
Therefore, it will be necessary to make a formula so that the link to the contest is always clickable. To do this, enter the new column "Link to CD" and insert the following formula in the first (after the headers) cell:
Columns C, D, E can be collapsed so as not to interfere.
Enjoy your use!
But I'll start with the story.
Now I work for a company with 100 percent state participation, the founder is one respected state corporation.
A year ago, I launched a new process here - participation in public procurement. Yes - yes, that’s how it happens, the state company did not participate in government procurements.
Attempts to launch it were, but unsuccessful.
When I came to work in this company, someone monitored purchases, but the sales department did not receive tenders from this someone, although they sent them keywords on the subject of the department. The process was broken.
I was at a loss for a while, until I decided to restart this process, asking me to pay for an account on Kontur.
I set everything up and began to monitor it myself. I found contests, gave it to the sales department, spun, spun.
People like when something happens. Sales department caught fire.
If you briefly describe what you need to start the process of participation in public procurement, then you need to:
1. Set up monitoring of public procurement
2. So that someone selects projects, understanding the Federal Law and reading all the requirements. Remove excess.
3. Keep records of the status of projects in a single interface.
4. Prepare an application for participation in the competition, understanding the structure of your price
5. Have accounts on electronic trading floors (ETP)
6. Be able to write complaints to the FAS on demolition of tenders (lawyer)
7. Analyze your market, I’m sorry
My account in Kontur helps me a lot. Purchases do p.p. 1-3 and 7. I already wrote earlier how to get data from Kontur. Purchasing at a minimum tariff .
But the account with them and similar services is paid. And not everyone can afford the acquisition and sit, tormented.
Here for you, my future millionaires, I sketched instructions on how to make the selection of contests more convenient and completely free.
- Take the RSS feed of contests from zakupki.gov.ru
- We will monitor this RSS using IFTTT and send it to Google SpreadSheets (they are also Google. Tables)
- We format the tables so that the name of the competition and the maximum price of the contract appear there
As a result, we get just such a tablet, the data in which will be updated independently:
Let's go!
1. RSS feed of contests from zakupki.gov.ru
Come on zakupki.gov.ru/epz/order/extendedsearch/search.html
It is important from extendedsearch, because the results produced by quicksearch differ from the extended ones. In particular, in quicksearch the system does not substitute the name of the customer, but we want to display it on the tablet. We use extended.
So, in the search form, enter:
- the search words to search for the contest. In my case, this is “fare payment”
- the procurement phase. We are interested in "Submitting an Application", the rest are disabled.
Click "Find."
The system will display the search results. If this is what you need, we get an RSS feed with updates for this request by clicking on the RSS icon.
Copy yourself the URL of the RSS feed [in our case - zakupki.gov.ru/tinyurl/ab14226a-5b2a-4de6-b283-112b6972f6bc ]
2. Monitoring RSS using IFTTT
Go to IFTTT .
If you are not registered, then do it and go to “My Applets”.
Press the “New Applet” button to create a new applet (trigger and action).
On the next screen, click on the "+ this" part, the system will prompt you to select a service. In the search bar, type “rss” or find the “RSS Feed” trigger in the service feed.
Click on the “RSS Feed” square.
2 options will appear, select “New feed item”.
The trigger will work if a new object appears in the RSS feed
- ТЕЛО_ОБЪЕКТА
On the trigger creation screen, insert the URL of our RSS feed and click "Create trigger".
Next, click the "+ that" button.
On the action search screen, write “sheets” and click on the found action.
On the next screen, select the option “Add row to spreadsheet” (add a row to the table).
Configure IFTT how to fill in the table.
Spreadsheet name - the name of the table file. If such a table does not exist, then IFTTT will create it on your disk. You can see the table in your list of Google tables .
Formatted row - the rule for filling the line. We select the fields we need, separating them with the ||| sign.
Fill in
{{EntryPublished}} ||| {{EntryTitle}} ||| {{EntryUrl}} ||| {{EntryContent}} ||| zakupki.gov.ru {{FeedUrl}} ||| {{EntryAuthor}}
That is, the following will be written in the cells:
DATE ||| COMPETITION NAME ||| Record URL ||| COMPETITION DESCRIPTION ||| RSS feed ||| CUSTOMER NAME
Drive folder path - the name of the folder on your Google Drive , in which the file with the table will be placed. I put all the tables in the zakupki folder.
Click "Create action".
On the final screen, select the name for the IFTTT rule.
And click Finish.
Everything, the rule has been created, the table will begin to fill up as soon as new contests appear in the RSS feed.
3. Format the tables
Paste the headings above the columns. Here's what we see: It
remains to select from the description the name of the competition and the initial price.
Add the column “Competition Name” and insert the following formula in the first (after the headings) cell:
=ArrayFormula(IF(ISBLANK($C$2:$C);"";SUBSTITUTE(SUBSTITUTE(MID(($D$2:$D);FIND("Наименование объекта закупки: ";($D$2:$D))+LEN("Наименование объекта закупки: ");FIND("
Размещение выполняется по";($D$2:$D))-FIND("Наименование объекта закупки: ";($D$2:$D))-LEN("Наименование объекта закупки: "));"«";"");"»";"")))The formula will parse the column D (description) and automatically fill in the name of the contest in this column when a new line appears in the table.
Add a column (enter the name of the column "Starting Price") and insert the following formula in the first (after the headings) cell:
=ArrayFormula(IF(ISBLANK($C$2:$C);"";MID(($D$2:$D);
IFERROR(FIND("Начальная цена контракта: ";($D$2:$D))+35;FIND("Начальная цена: ";($D$2:$D))+25);
FIND(" Валюта";($D$2:$D))-IFERROR(FIND("Начальная цена контракта: ";($D$2:$D))+35;FIND("Начальная цена: ";($D$2:$D))+25)
)))And the final touch. It happens that a link comes in two forms. With and without a domain:
http://zakupki.gov.ru/223/purchase/public/purchase/info/common-info.html?regNumber=31907911258
/epz/order/notice/ea44/view/common-info.html?regNumber=0818200000219000092
Therefore, it will be necessary to make a formula so that the link to the contest is always clickable. To do this, enter the new column "Link to CD" and insert the following formula in the first (after the headers) cell:
=ArrayFormula(IF(ISBLANK($C$2:$C);"";
IFERROR(
IF(FIND("epz";($C$2:$C));SUBSTITUTE(($C$2:$C);"/epz";"http://zakupki.gov.ru/epz"));
($C$2:$C))
))Columns C, D, E can be collapsed so as not to interfere.
Enjoy your use!