The requirements for each of the sales channel can be different such as for Ebay you might want to calculate Paypal fees; for Amazon you have to check for Amazon fees. For your own retail site, shipping cost might need different calculation as well as based on your target audience tax/hst will affect the price.

Use these code as a guidance and adjust as per your need.

Read the comments in the code. The comments explain what the code is doing. Check the previous articles in this series to make this article make more sense.

I will put the cell by cell code from Jupyter Notebook first; then will paste all the code in one block. If Medium does not maintain the indent, do it yourself when using the code.

This article will focus on Price calculation for Magento Based Retail Website. Calculation is not really platform dependent. At one point this data will be saved for Magento 2 platform to be uploaded.

Section Data Upload for Magento 2.

In [1]:

import pandas as pd

# output file will be dated

from datetime import date;today = date.today()today = today.strftime("%Y-%m-%d")today

Out[2]:

'2020-04-17'

In [3]:

data_folder = 'data-supplier-2020-04-14/';template_folder = './templates/'

In [4]:

input_file_name = 'all_supplier_data_unique_sorted_and_filtered.csv';raw_file = data_folder + input_file_name;raw_file

Out[4]:

'data-supplier-2020-04-14/all_supplier_data_unique_sorted_and_filtered.csv'

In [6]:

# read data from the combined sorted filtered file

raw_df = pd.read_csv(raw_file)raw_df.head(2)

Out[6]:

Unnamed: 0Product IDModel CodeFull Product NameShort Product NameProduct URLCategory NameCategory URLSubcategory NameSubcategory URL…Related ProductsRelated AccessoriesWeight KgHeight mmWidth mmDepth mmVideo linkRetail PriceStock statusDate Back0899230399A01AL3301111Black 3x3x3 MoYu AoLong V2 PuzzleBlack 3x3x3 MoYu AoLong V2 Puzzle

2 rows × 41 columns

In [7]:

# check product price and columns

raw_df[:1]['1pc Price'], raw_df.shape, raw_df.columns

Out[7]:

(0    12.9
 Name: 1pc Price, dtype: float64,
 (44911, 41),
 Index(['Unnamed: 0', 'Product ID', 'Model Code', 'Full Product Name',
        'Short Product Name', 'Product URL', 'Category Name', 'Category URL',
        'Subcategory Name', 'Subcategory URL', 'Date Product Was Launched',
        'Main Product Picture', 'Currency', '1pc Price',
        '1pc Cheapest Postal Shipping Price',
        '1pc Cheapest Express Courier Shipping Price', 'EAN',
        'Soon Discontinued', 'Full Product Description Part 1',
        'Full Product Description Part 2', 'Short Description',
        'Additional Product Picture 1', 'Additional Product Picture 2',
        'Additional Product Picture 3', 'Additional Product Picture 4',
        'Additional Product Picture 5', 'Additional Product Picture 6',
        'Additional Product Picture 7', 'Additional Product Picture 8',
        'Additional Product Picture 9', 'Additional Product Picture 10',
        'Related Products', 'Related Accessories', 'Weight Kg', 'Height mm',
        'Width mm', 'Depth mm', 'Video link', 'Retail Price', 'Stock status',
        'Date Back'],
       dtype='object'))

In [10]:

# Now, sell prices and related will be calculated including sell prices for amazon and ebay

# The output column names are kept in a csv file.# Reading the column names from that template fileprice_calculation_template_file = template_folder  + 'price_calculations_template.csv'price_calculation_template = pd.read_csv(price_calculation_template_file)price_calculation_template.head(), price_calculation_template.columns

Out[10]:

(Empty DataFrame
 Columns: [product_id, sku, Name, raw_price, dummy, cheapest_courier_shipping, product_ean, Ship Risk, Price Risk, Total, Risk Return , Total with ship, Referral Fee, Paypal Fees, Final Value Fee, Insertion Fee , HST, EU Vat, Currency Risk, shopforsoul_profit, ebay_profit, Income Tax, shopforsoul_cost, MLP, Special List Price, special_from_date, special_to_date, ebay_cost, ebay_list_price, Buyer pay from Mall, Ebay Buyers Pay, Ebay vs Mall, NA-1, Amazon Final Value Fee, amazon_profit, amazon_our_cost, amazon_list_price, amazon_special_price, Amazon Final Value Fee USD, amazon_profit_usd, amazon_our_cost_usd, amazon_list_price_usd, amazon_special_price_usd, amazon_list_price_gbp, amazon_special_price_gbp, amazon_list_price_eu, amazon_special_price_eu, amazon_list_price_mxn, amazon_special_price_mxn, ama_safe_refund_amount, Weight, Stock status, _category, _root_category, Visibility, Original Weight, Height mm, Width mm, Depth mm, status, Retail Price, original in stock, R+shipp, Shop vs retail, Soon Discontinued, weight right, Ship Right, Is Normal, amazon_ca_minimum_price, amazon_usd_minimum_price, amazon_gbp_minimum_price, amazon_eu_minimum_price, amazon_mxn_minimum, amazon_ca_abs_minimum, expedited_cost]
 Index: []
 
 [0 rows x 75 columns],
 Index(['product_id', 'sku', 'Name', 'raw_price', 'dummy',
        'cheapest_courier_shipping', 'product_ean', 'Ship Risk', 'Price Risk',
        'Total', 'Risk Return ', 'Total with ship', 'Referral Fee',
        'Paypal Fees', 'Final Value Fee', 'Insertion Fee ', 'HST', 'EU Vat',
        'Currency Risk', 'shopforsoul_profit', 'ebay_profit', 'Income Tax',
        'shopforsoul_cost', 'MLP', 'Special List Price', 'special_from_date',
        'special_to_date', 'ebay_cost', 'ebay_list_price',
        'Buyer pay from Mall', 'Ebay Buyers Pay', 'Ebay vs Mall', 'NA-1',
        'Amazon Final Value Fee', 'amazon_profit', 'amazon_our_cost',
        'amazon_list_price', 'amazon_special_price',
        'Amazon Final Value Fee USD', 'amazon_profit_usd',
        'amazon_our_cost_usd', 'amazon_list_price_usd',
        'amazon_special_price_usd', 'amazon_list_price_gbp',
        'amazon_special_price_gbp', 'amazon_list_price_eu',
        'amazon_special_price_eu', 'amazon_list_price_mxn',
        'amazon_special_price_mxn', 'ama_safe_refund_amount', 'Weight',
        'Stock status', '_category', '_root_category', 'Visibility',
        'Original Weight', 'Height mm', 'Width mm', 'Depth mm', 'status',
        'Retail Price', 'original in stock', 'R+shipp', 'Shop vs retail',
        'Soon Discontinued', 'weight right', 'Ship Right', 'Is Normal',
        'amazon_ca_minimum_price', 'amazon_usd_minimum_price',
        'amazon_gbp_minimum_price', 'amazon_eu_minimum_price',
        'amazon_mxn_minimum', 'amazon_ca_abs_minimum', 'expedited_cost'],
       dtype='object'))#price_calculation_template.columns#raw_df.columns, price_calculation_template.columns

In [11]:

# create a dataframe to store the prices

price_calculation_df = pd.DataFrame()

In [12]:

# take some column value as is : This block is not used any more

"""price_calculation_df[['product_id','sku','Name','raw_price','Postal Shipping Price','cheapest_courier_shipping','product_ean']] = raw_df[['Product ID','Model Code','Full Product Name','1pc Price','1pc Cheapest Postal Shipping Price','1pc Cheapest Express Courier Shipping Price','EAN']]"""

Out[12]:

"\nprice_calculation_df[\n        [\n        'product_id', \n        'sku', \n        'Name',\n        'raw_price', \n        'Postal Shipping Price',\n        'cheapest_courier_shipping', \n        'product_ean'\n        ]\n    ] = raw_df[\n            [\n                'Product ID', \n                'Model Code', \n                'Full Product Name',                \n                '1pc Price', \n                '1pc Cheapest Postal Shipping Price',\n                '1pc Cheapest Express Courier Shipping Price', \n                'EAN'                \n            ]\n        ]\n\n"

In [ ]:

# take some columns from the raw input dataframe to the output price calculation data frame# The terms A2, B2, C2 were used for a reason. Previously the calulations were being done in Excel and later converted to# Python code. To make the conversion easy, the cell names are kept intact. You can modify i.e. replace A2, B2, C2 with names you like,# you can even remove them and adjust the code with dataframe columns

In [13]:

# keep product id as isA2 = price_calculation_df['product_id'] = raw_df['Product ID']# modify product skuB2 = price_calculation_df['sku'] = 'shopforsoul-' + raw_df['Model Code']# create product name. added code to the name. this might for verification purpose such as to refer to the supplierC2 = price_calculation_df['Name'] = raw_df['Full Product Name'] + '-' + raw_df['Model Code']D2 = price_calculation_df['raw_price'] = raw_df['1pc Price']E2 = price_calculation_df['Postal Shipping Price'] = raw_df['1pc Cheapest Postal Shipping Price']F2 = price_calculation_df['cheapest_courier_shipping'] = raw_df['1pc Cheapest Express Courier Shipping Price']G2 = price_calculation_df['product_ean'] = raw_df['EAN']#price_calculation_df['product_ean'] [:-3]

In [14]:

list(price_calculation_df['Name'][:1])

Out[14]:

['Black 3x3x3 MoYu AoLong V2 Puzzle-A01AL3301111']

In [16]:

raw_df['1pc Cheapest Postal Shipping Price'][:2]

Out[16]:

0    No Postal Shipping Available
1    No Postal Shipping Available
Name: 1pc Cheapest Postal Shipping Price, dtype: object

In [21]:

# adjust cheapest_courier_shipping as supplied by supplier

In [22]:

# bring all F2 processing : cheapest_courier_shipping : replace no with $500 i.e. dificult and do not want to sell

F2 = F2.replace('NaN','500')F2 = F2.replace('nan','500')

In [23]:

#F2.unique()#No Courier Shipping Available, replace with 500#F2 = price_calculation_df['cheapest_courier_shipping'] = F2.replace('No Courier Shipping Available','500')#.astype(float)F2 = price_calculation_df['cheapest_courier_shipping'] = F2.replace('No Postal Shipping Available','500')#.astype(float)F2 = price_calculation_df['cheapest_courier_shipping'] = F2.astype(float)F2 = price_calculation_df['cheapest_courier_shipping'] = price_calculation_df['cheapest_courier_shipping'].replace(',','')

In [24]:

price_calculation_df['cheapest_courier_shipping'][:5]

Out[24]:

0    500.0
1    500.0
2    500.0
3    500.0
4    500.0
Name: cheapest_courier_shipping, dtype: float64

In [25]:

# adjust cheapest_courier_shipping as provided by the supplier

In [26]:

E2 = price_calculation_df['Postal Shipping Price'] = E2.replace('No Postal Shipping Available','500')E2 = price_calculation_df['Postal Shipping Price'] = E2.astype(float)E2 = price_calculation_df['Postal Shipping Price'] = price_calculation_df['Postal Shipping Price'].replace(',','')

In [28]:

sorted(F2, reverse=True)[:5]

Out[28]:

[500.0, 500.0, 500.0, 500.0, 500.0]

In [30]:

# check the output file so farprice_calculation_df.head(2)

Out[30]:

product_idskuNameraw_pricePostal Shipping Pricecheapest_courier_shippingproduct_ean030399shopforsoul-A01AL3301111Black 3x3x3 MoYu AoLong V2 Puzzle-A01AL330111112.90500.0500.06.941378e+12138649shopforsoul-A01AL3303110Qiyun AoLong V2 3x3x3 Speed Cube Enhanced Edit…13.61500.0500.06.941378e+12

In [31]:

# adjust product EAN. Product EAN are matched with Amazon to find the Amazon Product ID : ASING2 = price_calculation_df['product_ean'] = price_calculation_df['product_ean'].astype(str)price_calculation_df['product_ean'][:4]

Out[31]:

0    6941377886603.0
1    6941377965988.0
2    6941377972443.0
3    6941377953466.0
Name: product_ean, dtype: object

In [32]:

type(F2)

Out[32]:

pandas.core.series.Series

In [33]:

# define some constants to calculate your target price such as currency rates, shipping risk, hst, your tax burden. Some below are in percentages

In [34]:

ship_risk_percent = 0.03price_risk_percent = 0.03return_risk_percent = 0.05 #or max $XXmax_return_amount = 5referral_rate = 0.001paypal_fee_rate = 0.001final_value_fee_rate = 0.001insertion_fee_rate = 0.001hst_rate = 0.001eu_vat_rate = 0.001currency_risk = 0.001sfs_profit = 0.001ebay_profit = 0.001income_tax = 0.001sfs_special_profit = 0.001special_from_date = '2020-04-03'special_to_date = '2020-06-30'amazon_final_fee_rate = 0.001amazon_profit = 0.001amazon_special_profit = 0.001cad_to_usd = 0.704114cad_to_gbp = 0.573651cad_to_euro = 0.651656cad_to_mxn = 17.5915min_raw_price_to_show_on_amazon = 10

In [ ]:

# adjust for the case that shipping rate/cost may change

In [36]:

# D2 = price_calculation_df['raw_price'] = raw_df['']# price_calculation_df['cheapest_courier_shipping'].whereH2 = price_calculation_df['Ship Risk'] = F2 * ship_risk_percent #price_calculation_df['raw_price']price_calculation_df['Ship Risk'][:5]

Out[36]:

0    15.0
1    15.0
2    15.0
3    15.0
4    15.0
Name: Ship Risk, dtype: float64

In [ ]:

H2 = price_calculation_df['Ship Risk'] = F2 * ship_risk_percent #price_calculation_df['raw_price']

In [37]:

# adjust for the case that supplier price  may changeI2 = price_calculation_df['Price Risk'] = price_calculation_df['raw_price'] * price_risk_percent

In [38]:

# calculate total product price so farJ2 = price_calculation_df['Total'] = round(D2 + F2 + H2 + I2,2) #price_calculation_df['raw_price'] + price_calculation_df['Ship Risk'] + price_calculation_df['Price Risk']

Out[38]:

(0    12.90
 1    13.61
 2    10.20
 3    10.12
 4    10.96
 Name: raw_price, dtype: float64, 0    500.0
 1    500.0
 2    500.0
 3    500.0
 4    500.0
 Name: cheapest_courier_shipping, dtype: float64, 0    500.0
 1    500.0
 2    500.0
 3    500.0
 4    500.0
 Name: cheapest_courier_shipping, dtype: float64, 0    15.0
 1    15.0
 2    15.0
 3    15.0
 4    15.0
 Name: cheapest_courier_shipping, dtype: float64, 0    0.3870
 1    0.4083
 2    0.3060
 3    0.3036
 4    0.3288
 Name: raw_price, dtype: float64, 0    528.29
 1    529.02
 2    525.51
 3    525.42
 4    526.29
 dtype: float64)

In [39]:

# verify the total price so far

price_calculation_df['raw_price'][:5], F2[:5], price_calculation_df['cheapest_courier_shipping'][:5], H2[:5], I2[:5], J2[:5]

Out[39]:

(0    12.90
 1    13.61
 2    10.20
 3    10.12
 4    10.96
 Name: raw_price, dtype: float64, 0    500.0
 1    500.0
 2    500.0
 3    500.0
 4    500.0
 Name: cheapest_courier_shipping, dtype: float64, 0    500.0
 1    500.0
 2    500.0
 3    500.0
 4    500.0
 Name: cheapest_courier_shipping, dtype: float64, 0    15.0
 1    15.0
 2    15.0
 3    15.0
 4    15.0
 Name: cheapest_courier_shipping, dtype: float64, 0    0.3870
 1    0.4083
 2    0.3060
 3    0.3036
 4    0.3288
 Name: raw_price, dtype: float64, 0    528.29
 1    529.02
 2    525.51
 3    525.42
 4    526.29
 dtype: float64)

In [40]:

# adjust for the case that some products will be returned back. Account for related expenses

In [41]:

#price_calculation_df['Risk Return'] = []risk_return = []for aPrice in F2:#print(float(aPrice) * return_risk_percent)try:risk_amount = float(aPrice) * return_risk_percentrisk_amount = round(risk_amount, 2)except:risk_amount = max_return_amountif risk_amount  < max_return_amount:risk_return.append(  risk_amount )else:risk_return.append(max_return_amount)price_calculation_df['Risk Return'] = risk_returnK2 = price_calculation_df['Risk Return'] #= 10 #sorted([ int(price_calculation_df['cheapest_courier_shipping']), 10 ] )[1]K2 = risk_returnK2[:5]

Out[41]:

[20, 20, 20, 20, 20]

In [42]:

# calculate your total cost (also how it affects your selling price) considering various factors

In [43]:

L2 = price_calculation_df['Total with ship'] = round(J2 + K2, 2) #price_calculation_df['Total'] +  price_calculation_df['Risk Return']L2[:5]M2 = price_calculation_df['Referral Fee'] = price_calculation_df['Total with ship'] * referral_rateN2 = price_calculation_df['Paypal Fees'] = 0.30 + price_calculation_df['Total with ship'] * paypal_fee_rateO2 = price_calculation_df['Final Value Fee'] = price_calculation_df['Total with ship'] *  final_value_fee_rateP2 = price_calculation_df['Insertion Fee'] = price_calculation_df['Total with ship'] * insertion_fee_rateQ2 = price_calculation_df['HST'] = price_calculation_df['Total with ship'] * hst_rateR2 = price_calculation_df['EU Vat'] = price_calculation_df['Total with ship'] * eu_vat_rateS2 = price_calculation_df['Currency Risk'] = price_calculation_df['Total with ship'] * currency_riskT2 = price_calculation_df['shopforsoul_profit'] = price_calculation_df['Total with ship'] * sfs_profit#ebay_profit = 0.20U2 = price_calculation_df['ebay_profit'] = price_calculation_df['Total with ship'] * ebay_profit#income_tax = 0.20V2 = price_calculation_df['Income Tax'] = price_calculation_df['shopforsoul_profit'] * income_taxM2[:5], V2[:5], U2[:5]

Out[43]:

(0    54.829
 1    54.902
 2    54.551
 3    54.542
 4    54.629
 Name: Total with ship, dtype: float64, 0    27.4145
 1    27.4510
 2    27.2755
 3    27.2710
 4    27.3145
 Name: shopforsoul_profit, dtype: float64, 0    109.658
 1    109.804
 2    109.102
 3    109.084
 4    109.258
 Name: Total with ship, dtype: float64)

In [44]:

# Output price (and related) for your magento or similar based retail shop# sale/special prices and dates

In [45]:

W2 = price_calculation_df['shopforsoul_cost'] = round(L2+M2+N2+P2+S2+V2,2)#W2 = price_calculation_df['shopforsoul_cost']X2 = price_calculation_df['MLP'] = round(W2+T2,2)Y2 = price_calculation_df['Special List Price'] = round(W2+L2*sfs_special_profit,2)Z2 = price_calculation_df['special_from_date'] = special_from_datespecial_to_date = '2020-12-31'AA2 = price_calculation_df['special_to_date'] = special_to_date

All Code in One Block. You might still need to adjust the indent

# # Section Data Upload for Magento 2

# In[1]:import pandas as pd# In[2]:# output file will be dated
from datetime import date;
today = date.today()
today = today.strftime(“%Y-%m-%d”)
today# In[3]:data_folder = ‘data-supplier-2020–04–14/’;
template_folder = ‘./templates/’# In[4]:input_file_name = ‘all_supplier_data_unique_sorted_and_filtered.csv’;
raw_file = data_folder + input_file_name;
raw_file# In[6]:# read data from the combined sorted filtered file
raw_df = pd.read_csv(raw_file)
raw_df.head(2)# In[7]:# check product price and columns
raw_df[:1][‘1pc Price’], raw_df.shape, raw_df.columns# In[10]:# Now, sell prices and related will be calculated including sell prices for amazon and ebay 
# The output column names are kept in a csv file.
# Reading the column names from that template file
price_calculation_template_file = template_folder + ‘price_calculations_template.csv’
price_calculation_template = pd.read_csv(price_calculation_template_file)
price_calculation_template.head(), price_calculation_template.columns# In[ ]:#price_calculation_template.columns# In[ ]:#raw_df.columns, price_calculation_template.columns# In[11]:# create a dataframe to store the prices
price_calculation_df = pd.DataFrame()# In[12]:# take some column value as is : This block is not used any more
“””
price_calculation_df[
 [
 ‘product_id’, 
 ‘sku’, 
 ‘Name’,
 ‘raw_price’, 
 ‘Postal Shipping Price’,
 ‘cheapest_courier_shipping’, 
 ‘product_ean’
 ]
 ] = raw_df[
 [
 ‘Product ID’, 
 ‘Model Code’, 
 ‘Full Product Name’, 
 ‘1pc Price’, 
 ‘1pc Cheapest Postal Shipping Price’,
 ‘1pc Cheapest Express Courier Shipping Price’, 
 ‘EAN’ 
 ]
 ]“””# In[ ]:# take some columns from the raw input dataframe to the output price calculation data frame
# The terms A2, B2, C2 were used for a reason. Previously the calulations were being done in Excel and later converted to
# Python code. To make the conversion easy, the cell names are kept intact. You can modify i.e. replace A2, B2, C2 with names you like, 
# you can even remove them and adjust the code with dataframe columns# In[13]:# keep product id as is
A2 = price_calculation_df[‘product_id’] = raw_df[‘Product ID’]# modify product sku
B2 = price_calculation_df[‘sku’] = ‘shopforsoul-’ + raw_df[‘Model Code’]# create product name. added code to the name. this might for verification purpose such as to refer to the supplier
C2 = price_calculation_df[‘Name’] = raw_df[‘Full Product Name’] + ‘-’ + raw_df[‘Model Code’]
D2 = price_calculation_df[‘raw_price’] = raw_df[‘1pc Price’]
E2 = price_calculation_df[‘Postal Shipping Price’] = raw_df[‘1pc Cheapest Postal Shipping Price’]
F2 = price_calculation_df[‘cheapest_courier_shipping’] = raw_df[‘1pc Cheapest Express Courier Shipping Price’]
G2 = price_calculation_df[‘product_ean’] = raw_df[‘EAN’]
#price_calculation_df[‘product_ean’] [:-3]# In[14]:list(price_calculation_df[‘Name’][:1])# In[16]:raw_df[‘1pc Cheapest Postal Shipping Price’][:2]# In[21]:# adjust cheapest_courier_shipping as supplied by supplier# In[22]:# bring all F2 processing : cheapest_courier_shipping : replace no with $500 i.e. dificult and do not want to sell
F2 = F2.replace(‘NaN’,’500')
F2 = F2.replace(‘nan’,’500')# In[23]:#F2.unique()
#No Courier Shipping Available, replace with 500
#F2 = price_calculation_df[‘cheapest_courier_shipping’] = F2.replace(‘No Courier Shipping Available’,’500')#.astype(float)
F2 = price_calculation_df[‘cheapest_courier_shipping’] = F2.replace(‘No Postal Shipping Available’,’500')#.astype(float)
F2 = price_calculation_df[‘cheapest_courier_shipping’] = F2.astype(float)
F2 = price_calculation_df[‘cheapest_courier_shipping’] = price_calculation_df[‘cheapest_courier_shipping’].replace(‘,’,’’)# In[24]:price_calculation_df[‘cheapest_courier_shipping’][:5]# In[25]:# adjust cheapest_courier_shipping as provided by the supplier# In[26]:E2 = price_calculation_df[‘Postal Shipping Price’] = E2.replace(‘No Postal Shipping Available’,’500')
E2 = price_calculation_df[‘Postal Shipping Price’] = E2.astype(float)
E2 = price_calculation_df[‘Postal Shipping Price’] = price_calculation_df[‘Postal Shipping Price’].replace(‘,’,’’)# In[28]:sorted(F2, reverse=True)[:5]# In[30]:# check the output file so far
price_calculation_df.head(2)# In[31]:# adjust product EAN. Product EAN are matched with Amazon to find the Amazon Product ID : ASIN
G2 = price_calculation_df[‘product_ean’] = price_calculation_df[‘product_ean’].astype(str)
price_calculation_df[‘product_ean’][:4]# In[32]:type(F2)# In[33]:# define some constants to calculate your target price such as currency rates, shipping risk, hst, your tax burden# In[34]:ship_risk_percent = 0.001
price_risk_percent = 0.001
return_risk_percent = 0.001
max_return_amount = 0.001
referral_rate = 0.001
paypal_fee_rate = 0.001
final_value_fee_rate = 0.001
insertion_fee_rate = 0.001
hst_rate = 0.001
eu_vat_rate = 0.001
currency_risk = 0.001
sfs_profit = 0.001
ebay_profit = 0.001
income_tax = 0.001
sfs_special_profit = 0.001
special_from_date = ‘2020–04–03’
special_to_date = ‘2020–06–30’
amazon_final_fee_rate = 0.001
amazon_profit = 0.001
amazon_special_profit = 0.001
cad_to_usd = 0.704114
cad_to_gbp = 0.573651
cad_to_euro = 0.651656
cad_to_mxn = 17.5915
min_raw_price_to_show_on_amazon = 0.001# In[ ]:# adjust for the case that shipping rate/cost may change# In[36]:# D2 = price_calculation_df[‘raw_price’] = raw_df[‘’]
# price_calculation_df[‘cheapest_courier_shipping’].where
H2 = price_calculation_df[‘Ship Risk’] = F2 * ship_risk_percent #price_calculation_df[‘raw_price’]
price_calculation_df[‘Ship Risk’][:5]# In[ ]:H2 = price_calculation_df[‘Ship Risk’] = F2 * ship_risk_percent #price_calculation_df[‘raw_price’]# In[37]:# adjust for the case that supplier price may change
I2 = price_calculation_df[‘Price Risk’] = price_calculation_df[‘raw_price’] * price_risk_percent# In[38]:# calculate total product price so far
J2 = price_calculation_df[‘Total’] = round(D2 + F2 + H2 + I2,2) #price_calculation_df[‘raw_price’] + price_calculation_df[‘Ship Risk’] + price_calculation_df[‘Price Risk’]# In[39]:# verify the total price so far
price_calculation_df[‘raw_price’][:5], F2[:5], price_calculation_df[‘cheapest_courier_shipping’][:5], H2[:5], I2[:5], J2[:5]# In[40]:# adjust for the case that some products will be returned back. Account for related expenses# In[41]:#price_calculation_df[‘Risk Return’] = []
risk_return = []
for aPrice in F2:
 #print(float(aPrice) * return_risk_percent)
 try:
 risk_amount = float(aPrice) * return_risk_percent
 risk_amount = round(risk_amount, 2)
 except:
 risk_amount = max_return_amount
 
 
 if risk_amount < max_return_amount:
 risk_return.append( risk_amount )
 else:
 risk_return.append(max_return_amount)
 
price_calculation_df[‘Risk Return’] = risk_return
K2 = price_calculation_df[‘Risk Return’] #= 10 #sorted([ int(price_calculation_df[‘cheapest_courier_shipping’]), 10 ] )[1]
K2 = risk_return
K2[:5]# In[42]:# calculate your total cost (also how it affects your selling price) considering various factors# In[43]:L2 = price_calculation_df[‘Total with ship’] = round(J2 + K2, 2) #price_calculation_df[‘Total’] + price_calculation_df[‘Risk Return’]
L2[:5]
M2 = price_calculation_df[‘Referral Fee’] = price_calculation_df[‘Total with ship’] * referral_rate
N2 = price_calculation_df[‘Paypal Fees’] = 0.30 + price_calculation_df[‘Total with ship’] * paypal_fee_rateO2 = price_calculation_df[‘Final Value Fee’] = price_calculation_df[‘Total with ship’] * final_value_fee_rate
P2 = price_calculation_df[‘Insertion Fee’] = price_calculation_df[‘Total with ship’] * insertion_fee_rate
Q2 = price_calculation_df[‘HST’] = price_calculation_df[‘Total with ship’] * hst_rate 
R2 = price_calculation_df[‘EU Vat’] = price_calculation_df[‘Total with ship’] * eu_vat_rate
S2 = price_calculation_df[‘Currency Risk’] = price_calculation_df[‘Total with ship’] * currency_risk 
T2 = price_calculation_df[‘shopforsoul_profit’] = price_calculation_df[‘Total with ship’] * sfs_profit
#ebay_profit = 0.20
U2 = price_calculation_df[‘ebay_profit’] = price_calculation_df[‘Total with ship’] * ebay_profit
#income_tax = 0.20
V2 = price_calculation_df[‘Income Tax’] = price_calculation_df[‘shopforsoul_profit’] * income_tax
M2[:5], V2[:5], U2[:5]# In[44]:# Output price (and related) for your magento or similar based retail shop
# sale/special prices and dates# In[45]:W2 = price_calculation_df[‘shopforsoul_cost’] = round(L2+M2+N2+P2+S2+V2,2)
#W2 = price_calculation_df[‘shopforsoul_cost’]
X2 = price_calculation_df[‘MLP’] = round(W2+T2,2)
Y2 = price_calculation_df[‘Special List Price’] = round(W2+L2*sfs_special_profit,2)
Z2 = price_calculation_df[‘special_from_date’] = special_from_date
special_to_date = ‘2020–12–31’
AA2 = price_calculation_df[‘special_to_date’] = special_to_date

Medium: https://medium.com/@SayedAhmedCanada

*** . *** *** . *** . *** . ***

Sayed Ahmed

BSc. Eng. in Comp. Sc. & Eng. (BUET)

MSc. in Comp. Sc. (U of Manitoba, Canada)

MSc. in Data Science and Analytics (Ryerson University, Canada)

Linkedin: https://ca.linkedin.com/in/sayedjustetc

Blog: http://Bangla.SaLearningSchool.com, http://SitesTree.com

Training Courses: http://Training.SitesTree.com

8112223 Canada Inc/Justetc: http://JustEtc.net

Facebook Groups/Forums to discuss (Q & A):

https://www.facebook.com/banglasalearningschool

https://www.facebook.com/justetcsocial

Get access to courses on Big Data, Data Science, AI, Cloud, Linux, System Admin, Web Development and Misc. related. Also, create your own course to sell to others. http://sitestree.com/training/