Program for calculating the minimum price of radio components in real time
The most important issue in the development of electronic devices is always the question "how much will it cost?" Moreover, it is advisable to know the exact answer before you get involved in the wilds of development and invested in it in time and money. Most often, developers focus on the cost of components.
In fact, the calculation of the cost of a list of parts is an unpleasant and routine process associated with long searches on on-line catalogs, calling distributors, correspondence and waiting for answers, and so on. and so on. But not even that is worse, but that it is an iterative process. Suddenly seeing that the cost was higher than acceptable, the developer begins to look for other options for circuitry and other components. And again the search .... And 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 there are search engines for radio components that collect data from many suppliers and provide them on-line in a form convenient for developers. A variety of services and search tools are also provided. For example, importing your BOM (bill of material, list of materials and accessories) into a search engine, followed by automated search on this list.
There are a lot of search engines for radio components. I opted for octopart.com.
From the rest, it stands out with very clear documentation for its remote search query services (query APIs).
The list of suppliers covered by the octopart search was also fully consistent with the list used before.
I am developing custom-made small batches of embedded microprocessor devices for various applications. My main competitive advantage is the speed and accuracy of the deadlines. This can only be achieved 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 searches for octopart.
It should be noted here that our goal so far is not to buy, but to find out the price, and there may even be price dynamics - depending on the options of our choice. We kind of research or test price variability from our efforts to optimize it. It is clear that the search engine, which is needed in this case, must be very flexible and efficient. Rigid on-line search forms and services tailored specifically for purchase are of little use here. Obviously you need an open and flexible standalone PC application.
I chose MS Access for this . This is one of the programs of the MS Office suite in its professional edition. Programming in MS Access is conducted in the VBA language, which means Visual Basic for applications in translation.
A feature of MS Access is its amazing flexibility and speed when working with small databases of up to several hundred thousand records. This is more than enough for us when working with BOMs. In MS Access, code compilation is instantaneous, the data and the program are contained in one single file; recently, to run the MS Access file, you do not need to purchase the entire MS Office. Just download the free executing engine.
Here is the look of my program, its Front End, so to speak:
The program is fully open. In the modules tab you will find all the source texts. All labels and button layouts are free to change. The only request is to get your Octopart Key. This is the Octopart API user key that is issued on request from Octopart .
I work in a program for creating electronic circuits and printed circuit boards Altium Designer. Like all programs of this kind Altium has the function of generating BOM according to the scheme created in it. For BOM, for compatibility with the program, price calculations have 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 in accordance with 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 that will be searched.
Then I set the number of parties 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 decline sharply from a certain number of purchased parts of the same face value. By varying the number of boards (parties), you can find the optimum cost price of one board for given budget constraints.
Another nuance is that the stocks may not always have the quantity you specify, or the seller does not agree to sell such a small number of components. Then again you have to vary the value in the Quant.multiplier field to get the maximum number of positions found.
By the way, the BOM table in the program is hierarchical. By clicking on the plus sign in the left part of the program, you will see a subtable of the selected row, where there will be a list of all the offers found.
And in conclusion, it remains to click the Recalc lowest price and BOM cost button .
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 fieldand Quant.multiplier values . The proposal should be no less. The seller’s requirement for a minimum order quantity of components is also taken into account.
After that , the total BOM price for one batch appears in the BOM cost field .
Important! For the correct price conversion, you need to edit the tbl_CurrRates table . This table does not list all currencies, but only those that I had to deal with when searching. The table can be supplemented.
Since the program and data are stored together in the Access file, there are no such things as the client and server parts, installation, dependence on the location of directories, etc. You simply copy the file to the folder of the working draft of the printed circuit board, rename it as you like, import the BOM and click scan. The Access file is copied as many times as you have projects. And you can even aggregate using links to external tables in Access BOMs from several projects in order to reduce logistics costs for separate purchases for different projects. True, this is a topic for another project.
Scanning 110 records of demo data from the program that is laid out below takes no more than 59 seconds. You can double-check the cost of the BOM every day, taking into account changes in the exchange rate, or you can add other expenses or components to the BOM that the circuit design program does not add.
Advanced users are free to change the algorithm for calculating the minimum price based on criteria to minimize the number of different suppliers, reduce customs duties or reduce logistics costs.
Link to the program file with demo data .
In fact, the calculation of the cost of a list of parts is an unpleasant and routine process associated with long searches on on-line catalogs, calling distributors, correspondence and waiting for answers, and so on. and so on. But not even that is worse, but that it is an iterative process. Suddenly seeing that the cost was higher than acceptable, the developer begins to look for other options for circuitry and other components. And again the search .... And 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 there are search engines for radio components that collect data from many suppliers and provide them on-line in a form convenient for developers. A variety of services and search tools are also provided. For example, importing your BOM (bill of material, list of materials and accessories) into a search engine, followed by automated search on this list.
There are a lot of search engines for radio components. I opted for octopart.com.
From the rest, it stands out with very clear documentation for its remote search query services (query APIs).
The list of suppliers covered by the octopart search was also fully consistent with the list used before.
I am developing custom-made small batches of embedded microprocessor devices for various applications. My main competitive advantage is the speed and accuracy of the deadlines. This can only be achieved 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 searches for octopart.
It should be noted here that our goal so far is not to buy, but to find out the price, and there may even be price dynamics - depending on the options of our choice. We kind of research or test price variability from our efforts to optimize it. It is clear that the search engine, which is needed in this case, must be very flexible and efficient. Rigid on-line search forms and services tailored specifically for purchase are of little use here. Obviously you need an open and flexible standalone PC application.
I chose MS Access for this . This is one of the programs of the MS Office suite in its professional edition. Programming in MS Access is conducted in the VBA language, which means Visual Basic for applications in translation.
A feature of MS Access is its amazing flexibility and speed when working with small databases of up to several hundred thousand records. This is more than enough for us when working with BOMs. In MS Access, code compilation is instantaneous, the data and the program are contained in one single file; recently, to run the MS Access file, you do not need to purchase the entire MS Office. Just download the free executing engine.
Here is the look of my program, its Front End, so to speak:
What does the program do
- Imports BOM from an Excel spreadsheet (yes, MS Office is still needed, but the program is open, you can 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 number of purchased batches of parts;
- Allows you to select an arbitrary column in your BOM from which the names of the components you are looking for will be extracted;
- Allows you to specify a search criterion: exact match or flexible;
- Allows to exclude arbitrary strings from the search at will and prohibit price updating for them;
- Searches the Octopart database for one specified row or immediately throughout the list;
- For each row involved in the search, inserts the minimum price found;
- Converts currencies into your national currency;
- Calculates the entire cost of the BOM in your currency.
The program is fully open. In the modules tab you will find all the source texts. All labels and button layouts are free to change. The only request is to get your Octopart Key. This is the Octopart API user key that is issued on request from Octopart .
Typical application scenario
I work in a program for creating electronic circuits and printed circuit boards Altium Designer. Like all programs of this kind Altium has the function of generating BOM according to the scheme created in it. For BOM, for compatibility with the program, price calculations have several simple requirements:
- Contain a heading with column names
- contain a column with the name quantity;
- in which the number of parts of a given nominal value is recorded in a batch;
- Contain a column with a reasonably accurate name for the component. A search will be organized for this name.
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 in accordance with the contents of the BOM, and additional columns are added to it:
- Lowest price - minimum price for all found positions with distributors;
- Seller - seller’s name;
- Found MPN - a detailed description of the component obtained from Octopart;
- In Stock Quantity - the quantity of components with a given price available at the seller’s warehouse;
- Fix price - putting 1 in this field, we disconnect this line from searching and updating the price, for example, when we find it out from other sources;
- Product URL - link to the component seller’s offer page.
Next, in the search by field selection list, I select the field in the BOM table that will be searched.
Then I set the number of parties 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 decline sharply from a certain number of purchased parts of the same face value. By varying the number of boards (parties), you can find the optimum cost price of one board for given budget constraints.
Another nuance is that the stocks may not always have the quantity you specify, or the seller does not agree to sell such a small number of components. Then again you have to vary the value in the Quant.multiplier field to get the maximum number of positions found.
By the way, the BOM table in the program is hierarchical. By clicking on the plus sign in the left part of the program, you will see a subtable of the selected row, where there will be a list of all the offers found.
And in conclusion, it remains to click the Recalc lowest price and BOM cost button .
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 fieldand Quant.multiplier values . The proposal should be no less. The seller’s requirement for a minimum order quantity of components is also taken into account.
After that , the total BOM price for one batch appears in the BOM cost field .
Important! For the correct price conversion, you need to edit the tbl_CurrRates table . This table does not list all currencies, but only those that I had to deal with when searching. The table can be supplemented.
Since the program and data are stored together in the Access file, there are no such things as the client and server parts, installation, dependence on the location of directories, etc. You simply copy the file to the folder of the working draft of the printed circuit board, rename it as you like, import the BOM and click scan. The Access file is copied as many times as you have projects. And you can even aggregate using links to external tables in Access BOMs from several projects in order to reduce logistics costs for separate purchases for different projects. True, this is a topic for another project.
Scanning 110 records of demo data from the program that is laid out below takes no more than 59 seconds. You can double-check the cost of the BOM every day, taking into account changes in the exchange rate, or you can add other expenses or components to the BOM that the circuit design program does not add.
Advanced users are free to change the algorithm for calculating the minimum price based on criteria to minimize the number of different suppliers, reduce customs duties or reduce logistics costs.
Link to the program file with demo data .