Lazy Programmer's Guide to Automating Price Calculations

Introduction: Automating Price Calculations for the Lazy (and Efficient) Programmer

Let's be honest: programmers, whether seasoned or aspiring, are driven by a certain kind of laziness. But this isn't the couch-potato kind; it's the kind that fuels innovation — finding ways to get more done with less effort. Why spend hours on repetitive tasks when a few lines of code can do the job for you?

This mindset has been my guiding star as I navigate my dual roles. On my spare time, I code; at nine-to-five (seven-thirty-to-five-thirty, actually), I manage my dad's paint store. Among my various responsibilities, one of the most daunting is calculating the cost and selling prices of our products. Now, if you're familiar with Brazil's tax system, you'll understand the gravity of this task. For those who aren't, let me paint you a picture.

In Brazil, taxes are a labyrinthine mess. Beyond federal tax rates, each state has the liberty to set its own tax rates, creating a patchwork of regulations that would make any sane person’s head spin. Enter 'substituição tributária' (ST) — a regime where the tax burden is transferred from the seller to the manufacturer or importer. The idea is to streamline tax collection, but in practice, it often complicates matters further, especially when products cross state lines.

Calculating these costs manually is not just tedious; it's a soul-crushing endeavor. The repetitive nature of punching numbers into a calculator, cross-referencing tax tables, and double-checking figures is enough to make anyone yearn for a better way. That's where my programmer's "laziness" kicked in. I saw a clear need for automation — a way to take these convoluted calculations and simplify them into a process that’s as effortless as possible.

Thus, the web-based price calculator project was born. Armed with formulas from my accountant and a determination to streamline our pricing workflow, I set out to build a tool that could handle the heavy lifting. This post will take you through the journey of its creation, detailing the code, the challenges, and the solutions. Along the way, I’ll share insights into how you too can leverage programming to simplify complex tasks in your work or business. So, let's dive in and explore how we can turn lazy into efficient.

The Accountant's Formulas: Transforming Tedious Calculations into Computable Algorithms

In the world of business, especially one as intricate as a paint store, pricing products accurately is crucial. My accountant, the wizard behind our financial curtain, provided me with a set of formulas to calculate our costs and selling prices. These formulas consider various taxes and additional charges that are part and parcel of Brazil's complex tax landscape.

The general formula for calculating the cost is as follows:

Cost Price = Unit Price + ICMS(%) + IPI(%) + Freight(%)
Selling Price = Cost Price + Markup(%) + Additional(%) + Federal Tax (fixed at 7%)

It’s important to note that each percentage is applied on top of the previous values, compounding the total cost incrementally.

But it's not as straightforward as plugging numbers into a formula. The calculation of ICMS (a state tax) depends on whether the product falls under 'substituição tributária' (ST) or not, and this is where things get interesting.

For products without ST (identified by CFOP codes 5101, 5102, 6101, and 6102):

• If the ICMS rate is 4%, the effective ICMS in the formula becomes 13.27%.
• If the ICMS rate is 12%, the effective ICMS in the formula becomes 7.32%.

For products with ST (identified by CFOP codes 5401, 5403, 5405, 6401, 6403, and 6405):

• If the IPI rate is 0%, the ICMS in the formula is 19.7%.
• If the IPI rate is 1.3%, the ICMS in the formula is 20.3%.
• If the IPI rate is 3.25%, the ICMS in the formula is 21.26%.
• If the IPI rate is 6.5%, the ICMS in the formula is 24%.

I don’t entirely understand the logic behind these values — they’re the magic numbers handed down by my accountant. But I trust her expertise, and these are the values we need to work with.

Manual Calculations: A Tedious Routine

Before automation, the process was as follows:

1. Gather Information: Collect the unit price, ICMS rate, IPI rate, freight percentage (if any), markup percentage, and additional charges.
2. Apply Formulas: Using the provided formulas, manually calculate the effective ICMS rate based on the CFOP code and applicable ICMS/ IPI rates.
3. Calculate Cost Price: Add up all the percentages and apply them to the unit price.
4. Calculate Selling Price: Add the calculated cost price to the markup, additional charges, and federal tax.
5. **Repeat: **Perform these steps for each product, using a calculator to ensure accuracy.

This manual process was not only time-consuming but also prone to errors. Every miscalculation could lead to incorrect pricing, impacting our profit margins or making us uncompetitive.

From Manual to Algorithmic

The beauty of these steps is that they form an algorithm, a series of computable steps. And this is where automation shines. By translating these steps into code, I could create a tool that performs these calculations instantly and accurately. No more tedious number crunching, no more manual errors — just a streamlined, efficient process that ensures we get our pricing right every time.

In the next section, I'll walk you through how I transformed these formulas into a web-based price calculator, turning hours of work into a task that takes mere seconds.

Implementing the First Part: From Supplier Invoices to a Functional MVP

In our paint store, every supplier sends us invoices in the form of XML files. These XML files are standardized by the Federal Revenue Service of Brazil, ensuring they follow a consistent structure. This consistency makes it feasible to automate the extraction of necessary data for our price calculations.

Technologies Used

To build this solution, we chose a stack that includes:

• Flask: A lightweight web framework for Python that makes it easy to set up routes and handle requests.
• Jinja2: The templating engine used by Flask to render HTML pages dynamically.
• JavaScript (with jQuery): To handle client-side interactions and make AJAX requests for dynamic updates.

Setting Up Routes

For the initial implementation, we set up two main routes: indexand calculate.

indexRoute

The index route serves the main page of our application. It renders the form that allows users to upload an XML file, input the markup percentage, additional charges, and freight costs. We also set default values for these inputs to streamline the user experience.

@app.route('/')
def index():
    return render_template('index.html', product_data=[], markup=50, additional=5, freight=0, include_tax=False)

calculateRoute

The calculateroute processes the uploaded XML file. It extracts the necessary data, applies the formulas provided by my accountant, and calculates the cost and selling prices. This route then renders the results back to the index.html template.

@app.route('/calculate', methods=['POST'])
def calculate():
    if 'xmlFile' not in request.files:
        return redirect(url_for('index'))

    file = request.files['xmlFile']
    if file.filename == '':
        return redirect(url_for('index'))

    markup = float(request.form['markup'])
    additional = float(request.form['additional'])
    freight = float(request.form['freight'])
    include_tax = 'federalTax' in request.form

    namespaces = {'nfe': 'http://www.portalfiscal.inf.br/nfe'}
    product_data = extract_data_from_xml(file, namespaces)

    for product in product_data:
        cost_price = calculate_cost_price(product['Valor Unitário'], product['Alíq. ICMS'], product['Alíq. IPI'], product['CFOP'], freight)
        selling_price = calculate_selling_price(cost_price, markup, additional, include_tax)
        product['Preço de Custo Final'] = cost_price
        product['Preço de Venda'] = selling_price

    return render_template('index.html', product_data=product_data, markup=markup, additional=additional, freight=freight, include_tax=include_tax)

Crafting the MVP

The Minimum Viable Product (MVP) focused on getting the basic functionality right. This included:

1. Form Setup: An HTML form for uploading XML files and inputting calculation parameters (markup, additional charges, freight, and federal tax inclusion).
2. Data Extraction: Using Python's xml.etree.ElementTree to parse the XML files and extract relevant product data.
3. Calculation Logic: Implementing the cost and selling price formulas in Python.
4. Result Display: Dynamically rendering the calculated prices back to the user through the HTML template.

Here’s the index.html template for the form and result display:

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <title>Calculadora de Preço de Venda</title>
    <link rel="stylesheet" href="/static/style.css">
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script type="application/json" id="productData">
        {{ product_data|tojson }}
    </script>
    <script type="application/json" id="additionalData">
        {{ additional }}
    </script>
    <script type="application/json" id="freightData">
        {{ freight }}
    </script>
    <script type="application/json" id="includeTaxData">
        {{ include_tax|tojson }}
    </script>
    <script src="/static/script.js"></script>
</head>
<body>
    <h1>Calculadora de Preço de Venda</h1>
    <form action="/calculate" method="post" enctype="multipart/form-data">
        <label for="xmlFile">Carregar XML:</label>
        <input type="file" id="xmlFile" name="xmlFile" accept=".xml"><br><br>

        <label for="markup">Markup (%):</label>
        <input type="number" id="markup" name="markup" step="0.1" value="50"><br><br>

        <label for="additional">Valor Adicional (%):</label>
        <input type="number" id="additional" name="additional" step="0.1" value="5"><br><br>

        <label for="freight">Frete (%):</label>
        <input type="number" id="freight" name="freight" step="0.1" value="0"><br><br>

        <label for="federalTax">Incluir Imposto Federal (7%):</label>
        <input type="checkbox" id="federalTax" name="federalTax" checked><br><br>

        <button type="submit">Calcular</button>
    </form>

    {% if product_data %}
        <h2>Dados do Formulário:</h2>
        <p>
            Markup Selecionado: {{ markup }}%<br>
            Valor Adicional Selecionado: {{ additional }}%<br>
            Frete Adicionado: {{ freight }}%<br>
            Incluir Imposto Federal: {{ 'Sim' if include_tax else 'Não' }}
        </p>
        <h2>Cálculo dos Preços:</h2>
        <table border="1">
            <tr>
                <th>Produto</th>
                <th>CFOP</th>
                <th>Custo Unit</th>
                <th>Aliq. ICMS</th>
                <th>Aliq. IPI</th>
                <th>Frete</th>
                <th>Imposto Federal</th>
                <th>Markup (%)</th>
                <th>Custo Final</th>
                <th>Venda Final</th>
            </tr>
            {% for product in product_data %}
            <tr>
                <td>{{ product['Nome do Produto'] }}</td>
                <td>{{ product['CFOP'] }}</td>
                <td>R$ {{ product['Valor Unitário'] }}</td>
                <td>{{ product['Alíq. ICMS'] }}</td>
                <td>{{ product['Alíq. IPI'] }}</td>
                <td>{{ freight }}%</td>
                <td>{{ 'Sim' if include_tax else 'Não' }}</td>
                <td><input type="number" class="markup-input" step="0.1" value="{{ markup }}" data-index="{{ loop.index0 }}"></td>
                <td>R$ {{ '%.2f' | format(product['Preço de Custo Final']) }}</td>
                <td>R$ {{ '%.2f' | format(product['Preço de Venda']) }}</td>
            </tr>
            {% endfor %}
        </table>
    {% endif %}
</body>
</html>

With this MVP in place, we had a functional tool that could handle the complexities of our pricing calculations, saving us time and reducing the potential for errors. But this was just the beginning.

Adding Interactivity: Editable Markup Fields for Dynamic Price Updates

In the real world, the need for flexibility in pricing is paramount. Sometimes, different products on the same invoice require different markup percentages. Manually re-uploading the XML file and recalculating prices each time a markup needs to be adjusted is not only inefficient but also prone to errors. To address this, I introduced an interactive, editable markup field that updates the selling price automatically. This is where JavaScript comes into play.

Enhancing User Experience with JavaScript

To achieve this dynamic behavior, we needed to:

1. Make the markup field editable directly within the product table.
2. Use JavaScript to detect changes in the markup field and trigger recalculations.
3. Send the updated data to the server via AJAX and update the selling price without refreshing the page.

Here's a detailed breakdown of the implementation:

1. Updating the HTML Template

First, we needed to ensure the markup field in our HTML table was editable and had the necessary attributes for our JavaScript to identify and manipulate it.

<td><input type="number" class="markup-input" step="0.1" value="{{ markup }}" data-index="{{ loop.index0 }}"></td>

This line creates an input field within the table cell, sets its initial value to the current markup, and adds a data-indexattribute to keep track of the product's position in the list.

2. JavaScript for Real-Time Updates

We then added a JavaScript file to handle the dynamic updates. This script listens for changes in the markup input fields and makes AJAX requests to update the prices accordingly.

Here’s the content of static/script.js:

$(document).ready(function() {
    const productData = JSON.parse(document.getElementById('productData').textContent);
    const additional = JSON.parse(document.getElementById('additionalData').textContent);
    const freight = JSON.parse(document.getElementById('freightData').textContent);
    const includeTax = JSON.parse(document.getElementById('includeTaxData').textContent);

    $('.markup-input').on('input', function() {
        let markup = $(this).val();
        let index = $(this).data('index');
        updatePrices(markup, index);
    });

    function updatePrices(markup, index) {
        $.ajax({
            url: '/update_prices',
            method: 'POST',
            contentType: 'application/json',
            data: JSON.stringify({
                markup: markup,
                index: index,
                product_data: productData,
                additional: additional,
                freight: freight,
                include_tax: includeTax
            }),
            success: function(data) {
                $('table').find('tr').eq(index + 1).find('td').eq(9).text('R$ ' + data.new_selling_price.toFixed(2));
            }
        });
    }
});

This script does the following:

• Reads the product data and other relevant values from the hidden JSON elements in the HTML.
• Listens for input events on the markup fields.
• Sends an AJAX request to the server whenever a markup value changes, passing the updated markup and necessary data.
• Updates the displayed selling price with the new value returned from the server.

3. Handling AJAX Requests in Flask

We needed a new route in our Flask app to handle the AJAX requests and perform the necessary recalculations.

Here’s the updated main.py:

@app.route('/update_prices', methods=['POST'])
def update_prices():
    data = request.get_json()
    index = data['index']
    markup = float(data['markup'])
    additional = data['additional']
    freight = data['freight']
    include_tax = data['include_tax']
    product_data = data['product_data']

    product = product_data[index]
    cost_price = calculate_cost_price(product['Valor Unitário'], product['Alíq. ICMS'], product['Alíq. IPI'], product['CFOP'], freight)
    new_selling_price = calculate_selling_price(cost_price, markup, additional, include_tax)

    return jsonify({'new_selling_price': new_selling_price})

This route:

• Receives the updated markup and product data via a JSON payload.
• Recalculates the cost and selling prices using the same formulas as before.
• Returns the new selling price to the client, which JavaScript then updates in the table.

4. Adjusting the HTML Template

Finally, ensure that the HTML template includes the necessary scripts and hidden JSON data.

Here’s the updated index.html:

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <title>Calculadora de Preço de Venda</title>
    <link rel="stylesheet" href="/static/style.css">
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script type="application/json" id="productData">
        {{ product_data|tojson }}
    </script>
    <script type="application/json" id="additionalData">
        {{ additional }}
    </script>
    <script type="application/json" id="freightData">
        {{ freight }}
    </script>
    <script type="application/json" id="includeTaxData">
        {{ include_tax|tojson }}
    </script>
    <script src="/static/script.js"></script>
</head>
<body>
    <h1>Calculadora de Preço de Venda</h1>
    <form action="/calculate" method="post" enctype="multipart/form-data">
        <label for="xmlFile">Carregar XML:</label>
        <input type="file" id="xmlFile" name="xmlFile" accept=".xml"><br><br>

        <label for="markup">Markup (%):</label>
        <input type="number" id="markup" name="markup" step="0.1" value="50"><br><br>

        <label for="additional">Valor Adicional (%):</label>
        <input type="number" id="additional" name="additional" step="0.1" value="5"><br><br>

        <label for="freight">Frete (%):</label>
        <input type="number" id="freight" name="freight" step="0.1" value="0"><br><br>

        <label for="federalTax">Incluir Imposto Federal (7%):</label>
        <input type="checkbox" id="federalTax" name="federalTax" checked><br><br>

        <button type="submit">Calcular</button>
    </form>

    {% if product_data %}
        <h2>Dados do Formulário:</h2>
        <p>
            Markup Selecionado: {{ markup }}%<br>
            Valor Adicional Selecionado: {{ additional }}%<br>
            Frete Adicionado: {{ freight }}%<br>
            Incluir Imposto Federal: {{ 'Sim' if include_tax else 'Não' }}
        </p>
        <h2>Cálculo dos Preços:</h2>
        <table border="1">
            <tr>
                <th>Produto</th>
                <th>CFOP</th>
                <th>Custo Unit</th>
                <th>Aliq. ICMS</th>
                <th>Aliq. IPI</th>
                <th>Frete</th>
                <th>Imposto Federal</th>
                <th>Markup (%)</th>
                <th>Custo Final</th>
                <th>Venda Final</th>
            </tr>
            {% for product in product_data %}
            <tr>
                <td>{{ product['Nome do Produto'] }}</td>
                <td>{{ product['CFOP'] }}</td>
                <td>R$ {{ product['Valor Unitário'] }}</td>
                <td>{{ product['Alíq. ICMS'] }}</td>
                <td>{{ product['Alíq. IPI'] }}</td>
                <td>{{ freight }}%</td>
                <td>{{ 'Sim' if include_tax else 'Não' }}</td>
                <td><input type="number" class="markup-input" step="0.1" value="{{ markup }}" data-index="{{ loop.index0 }}"></td>
                <td>R$ {{ '%.2f' | format(product['Preço de Custo Final']) }}</td>
                <td>R$ {{ '%.2f' | format(product['Preço de Venda']) }}</td>
            </tr>
            {% endfor %}
        </table>
    {% endif %}
</body>
</html>

With these enhancements, we transformed a static form into an interactive tool that allows for real-time price adjustments. This not only streamlines our workflow but also provides flexibility in pricing, making the tool much more powerful and user-friendly. In the next section, I'll discuss potential future iterations and improvements to further enhance the functionality and usability of this project.

Future Iterations: Enhancing Functionality and User Experience

With the basic functionality in place, there are several enhancements that can make the price calculator even more robust and user-friendly. Here are the next steps I'm considering for future iterations:

1. Configurations Page

Currently, the tax rates and CFOP codes are hard-coded into the application. This approach works for an MVP, but it's not scalable. Tax rates can change, and new CFOP codes with different tax regimes can emerge. To address this, I plan to implement a configurations page where users can update these values directly within the app.

• Editable Tax Rates and CFOP Codes: Allow users to add, edit, and delete tax rates and CFOP codes.
• Persistent Settings: Store these configurations in a database to ensure they are retained between sessions.

2. Drag-and-Drop XML Upload

To improve the user experience, I want to add drag-and-drop functionality for XML uploads. This would make the process more intuitive and quicker.

• Drag-and-Drop Interface: Implement a drag-and-drop zone for users to easily upload XML files.
• Fallback to Button Upload: Maintain the current file upload button as a fallback option.

3. Hosting on Store’s Domain

Currently, the application is running on a Heroku server. To give it a more professional look and ensure better accessibility, I plan to host it on the store's domain.

• Custom Domain: Migrate the app to be hosted on the store's domain.
• Enhanced Accessibility: Ensure the app is easily accessible to all users, including employees and customers.

4. Improved Aesthetics with CSS

While functionality is king, aesthetics shouldn't be neglected. A well-designed interface can significantly enhance the user experience. I plan to use CSS to make the page more visually appealing.

• Modern UI Design: Implement a clean, modern design for the app using CSS.
• Responsive Design: Ensure the app works well on different devices, including desktops, tablets, and smartphones.

Conclusion

The journey of creating this web-based price calculator has been both challenging and rewarding. From automating tedious manual calculations to adding dynamic, real-time updates, each step has made the app more efficient and user-friendly. But the journey doesn't end here. With future iterations focusing on configurability, user experience, and professional hosting, the app will continue to evolve and serve its purpose even better.

Stay tuned for more updates as I continue to enhance and refine this project. If you're on a similar journey or have any suggestions, I'd love to hear from you! Let's keep innovating and making our lives (and jobs) a bit easier, one line of code at a time.