The program for calculating the minimum price of radio components in real time
The most important question in the development of electronic devices is always the question “how much will it cost?” Moreover, it is advisable to know the exact answer even before you were involved in the development mounds and invested in it with time and money. Most often, developers focus on the cost of components.
In fact, the calculation of the cost of the parts list is an unpleasant and routine process associated with long searches in on-line catalogs, calling distributors, correspondence and waiting for answers, and so on. and so on But not even that is worse, but the fact that this is an iterative process. Seeing suddenly that the cost was higher than acceptable, the developer begins to look for other options for circuit design and other components. And again searches ... And the routine is the enemy of the developer. To avoid it, many sacrifice optimality. But, fortunately, this problem can be alleviated.
For a long time on the Internet I exist search systems of radio components that collect data from many suppliers and provide them on-line in a form convenient for developers. Also various services and search tools are provided. For example, import into the search engine of your BOM (bill of material, list of materials and components), followed by automated search on this list.
There are a lot of search engines for radio components. I opted for octopart.com.
')
Of the rest, it stands out with very clear documentation for its remote search query services (API requests).
The list of providers covered by octopart search also fully corresponded to the list that was used before.
I develop by request small batches of embedded microprocessor devices for various applications. My main competitive advantage is speed and accuracy of terms. This can be achieved only if you use the services of global on-line distributors, such as Farnell, Moser, Digi-Key, Future, Avnet, TME, and so on. Just in the warehouses of these distributors and conducts a search octopart.
Here it should be noted that our goal so far is not to buy, but to find out the price, and even the dynamics of the price may be depending on the options of our choice. We kind of explore or test the variability of prices from our efforts to optimize it. It is clear that the search engine, which is needed in this case, must be very flexible and operational. Hard on-line search forms and services, sharpened specifically for the purchase, there are few fit. Obviously you need an open and flexible standalone application on the PC.
I chose for this MS Access . This is one of the programs in the MS Office package in its professional edition. Programming in MS Access is conducted in the VBA language, which means Visual Basic for Applications.
A feature of MS Access is its amazing flexibility and speed when working with small databases of up to several hundred thousand entries. This is more than enough for us when working with BOMs. In MS Access, the compilation of the code occurs instantly, the data and the program are contained in one single file. Recently, to run the MS Acce file, you do not need to purchase the entire MS Office. Enough to download the free performing engine.
Here is the look of my program, so to say its Front End:
The program is fully open. In the module tab you will find all the source code. All labels and button locations are free to change. The only request is to get your Octopart Key. This is the Octopart API user key issued on request at Octopart .
I work in the program for creating electronic circuits and printed circuit boards Altium Designer. Like all programs of this kind, Altium has the function of generating a BOM according to the scheme created in it. To be compatible with the BOM program, pricing has several simple requirements:
Here is an example of a BOM in Excel:
Having a BOM in Excel, I click the Import BOM button in the program.
The table in the program window is converted according to the contents of the BOM and additional columns are added to it:
Next in the Search by field selection list, I select the field in the BOM table, which will be used to search.
Then I set the number of games that interests me in the Quant.multiplier field. In the simplest case, this is the number of boards. This is an important parameter, since for all sellers the price begins to fall sharply from a certain amount of purchased parts of the same denomination. By varying the number of boards (batches), one can find the optimum cost of a single board under given budget constraints.
Another nuance is that the quantity you specify may not always be in stock, or the seller does not agree to sell such a small number of components. Then again it is necessary to vary the value in the Quant.multiplier field in order to get the maximum number of positions found.
By the way, the BOM table in the program is hierarchical. Clicking on the plus sign on the left side of the program, you will see a subtable of the selected row, where there will be a list of all the sentences found.
And finally it remains to press the button Recalc lowest price and BOM cost .
The program will go through all the records of price offers downloaded from Octopart and find the minimum prices for each component. This takes into account the number of components you specify, which is equal to the product of the quantity field and the Quant.multiplier value. The proposal must be no less. It also takes into account the seller’s requirement for a minimum order quantity of components.
After that, the total BOM price for one batch appears in the BOM cost field.
Important! To convert the price correctly, you need to edit the tbl_CurrRates table. In this table, not all currencies are listed, but only those that I had to deal with when searching. The table can be supplemented.
Since the Access program and data are stored together, there are no such things as client and server parts, installation, dependence on the location of directories, and so on. You simply copy the file to the folder of the working project of the circuit board, rename as you like, import the BOM and click the scan. The Access file is copied as many times as you have projects. Or, you can even aggregate using Access to the BOMs from several projects with links to external tables in order to reduce the logistic costs of separate purchases for different projects. True, this is a topic for another project.
Scanning 110 demo data records from a program that is laid out below takes no more than 59 seconds. You can recheck the cost of a BOM every day, bearing in mind currency rate changes, or you can add other expenses or components to a BOM that the design program does not add.
Advanced users are free to change the algorithm for calculating the minimum price based on the criteria of minimizing the number of different suppliers, reducing customs duties or reducing logistics costs.
Link to program file with sample data .
In fact, the calculation of the cost of the parts list is an unpleasant and routine process associated with long searches in on-line catalogs, calling distributors, correspondence and waiting for answers, and so on. and so on But not even that is worse, but the fact that this is an iterative process. Seeing suddenly that the cost was higher than acceptable, the developer begins to look for other options for circuit design and other components. And again searches ... And the routine is the enemy of the developer. To avoid it, many sacrifice optimality. But, fortunately, this problem can be alleviated.
For a long time on the Internet I exist search systems of radio components that collect data from many suppliers and provide them on-line in a form convenient for developers. Also various services and search tools are provided. For example, import into the search engine of your BOM (bill of material, list of materials and components), followed by automated search on this list.
There are a lot of search engines for radio components. I opted for octopart.com.
')
Of the rest, it stands out with very clear documentation for its remote search query services (API requests).
The list of providers covered by octopart search also fully corresponded to the list that was used before.
I develop by request small batches of embedded microprocessor devices for various applications. My main competitive advantage is speed and accuracy of terms. This can be achieved only if you use the services of global on-line distributors, such as Farnell, Moser, Digi-Key, Future, Avnet, TME, and so on. Just in the warehouses of these distributors and conducts a search octopart.
Here it should be noted that our goal so far is not to buy, but to find out the price, and even the dynamics of the price may be depending on the options of our choice. We kind of explore or test the variability of prices from our efforts to optimize it. It is clear that the search engine, which is needed in this case, must be very flexible and operational. Hard on-line search forms and services, sharpened specifically for the purchase, there are few fit. Obviously you need an open and flexible standalone application on the PC.
I chose for this MS Access . This is one of the programs in the MS Office package in its professional edition. Programming in MS Access is conducted in the VBA language, which means Visual Basic for Applications.
A feature of MS Access is its amazing flexibility and speed when working with small databases of up to several hundred thousand entries. This is more than enough for us when working with BOMs. In MS Access, the compilation of the code occurs instantly, the data and the program are contained in one single file. Recently, to run the MS Acce file, you do not need to purchase the entire MS Office. Enough to download the free performing engine.
Here is the look of my program, so to say its Front End:
What the program does
- Imports BOM from Excel spreadsheet (yes, MS Office is still needed, but the program is open, you can make an import from CSV or txt);
- Turns your BOM into a table in MS Access, which can be edited, and complements it with its necessary fields;
- Allows you to set the quantity of purchased parts;
- Allows you to select an arbitrary column in your BOM, from which the names of the desired components will be extracted;
- Allows you to specify the search criteria: exact or flexible;
- Allows you to optionally exclude arbitrary strings from the search and prohibit updating prices for them;
- Searches the Octopart database for one specified line or immediately for the entire list;
- For each line that participated in the search, inserts the minimum price found;
- Converts currencies to your national currency;
- Calculates the entire cost of a BOM in your currency.
The program is fully open. In the module tab you will find all the source code. All labels and button locations are free to change. The only request is to get your Octopart Key. This is the Octopart API user key issued on request at Octopart .
Typical application scenario
I work in the program for creating electronic circuits and printed circuit boards Altium Designer. Like all programs of this kind, Altium has the function of generating a BOM according to the scheme created in it. To be compatible with the BOM program, pricing has several simple requirements:
- contain a title with column names;
- contain a column named quantity;
- in which the number of parts of a given par value is recorded in the lot
- contain a column with a fairly accurate name of the component. This title will organize the search.
Here is an example of a BOM in Excel:
Having a BOM in Excel, I click the Import BOM button in the program.
The table in the program window is converted according to the contents of the BOM and additional columns are added to it:
- Lowest price - the minimum price for all items found by distributors;
- Seller - the name of the seller;
- Found MPN - a detailed description of the component, obtained from Octopart;
- In Stock Quantity - the number of components with this price that is available at the seller’s warehouse;
- Fix price - by setting this field to 1, we disable this string from searching and updating the price, for example, when we know it from other sources;
- Product URL - link to the offer page of the component from the final seller.
Next in the Search by field selection list, I select the field in the BOM table, which will be used to search.
Then I set the number of games that interests me in the Quant.multiplier field. In the simplest case, this is the number of boards. This is an important parameter, since for all sellers the price begins to fall sharply from a certain amount of purchased parts of the same denomination. By varying the number of boards (batches), one can find the optimum cost of a single board under given budget constraints.
Another nuance is that the quantity you specify may not always be in stock, or the seller does not agree to sell such a small number of components. Then again it is necessary to vary the value in the Quant.multiplier field in order to get the maximum number of positions found.
By the way, the BOM table in the program is hierarchical. Clicking on the plus sign on the left side of the program, you will see a subtable of the selected row, where there will be a list of all the sentences found.
And finally it remains to press the button Recalc lowest price and BOM cost .
The program will go through all the records of price offers downloaded from Octopart and find the minimum prices for each component. This takes into account the number of components you specify, which is equal to the product of the quantity field and the Quant.multiplier value. The proposal must be no less. It also takes into account the seller’s requirement for a minimum order quantity of components.
After that, the total BOM price for one batch appears in the BOM cost field.
Important! To convert the price correctly, you need to edit the tbl_CurrRates table. In this table, not all currencies are listed, but only those that I had to deal with when searching. The table can be supplemented.
Since the Access program and data are stored together, there are no such things as client and server parts, installation, dependence on the location of directories, and so on. You simply copy the file to the folder of the working project of the circuit board, rename as you like, import the BOM and click the scan. The Access file is copied as many times as you have projects. Or, you can even aggregate using Access to the BOMs from several projects with links to external tables in order to reduce the logistic costs of separate purchases for different projects. True, this is a topic for another project.
Scanning 110 demo data records from a program that is laid out below takes no more than 59 seconds. You can recheck the cost of a BOM every day, bearing in mind currency rate changes, or you can add other expenses or components to a BOM that the design program does not add.
Advanced users are free to change the algorithm for calculating the minimum price based on the criteria of minimizing the number of different suppliers, reducing customs duties or reducing logistics costs.
Link to program file with sample data .
Source: https://habr.com/ru/post/253879/
All Articles