| Line 159... |
Line 159... |
| 159 |
sheet.write(0, 4, "Brand", heading_xf)
|
159 |
sheet.write(0, 4, "Brand", heading_xf)
|
| 160 |
sheet.write(0, 5, "Model Name", heading_xf)
|
160 |
sheet.write(0, 5, "Model Name", heading_xf)
|
| 161 |
sheet.write(0, 6, "Model Number", heading_xf)
|
161 |
sheet.write(0, 6, "Model Number", heading_xf)
|
| 162 |
sheet.write(0, 7, "Color", heading_xf)
|
162 |
sheet.write(0, 7, "Color", heading_xf)
|
| 163 |
sheet.write(0, 8, "Weight", heading_xf)
|
163 |
sheet.write(0, 8, "Weight", heading_xf)
|
| - |
|
164 |
sheet.write(0, 9, "Courier Cost")
|
| 164 |
sheet.write(0, 9, "Risky", heading_xf)
|
165 |
sheet.write(0, 10, "Risky", heading_xf)
|
| 165 |
sheet.write(0, 10, "Our SP", heading_xf)
|
166 |
sheet.write(0, 11, "Our SP", heading_xf)
|
| 166 |
sheet.write(0, 11, "Our TP", heading_xf)
|
167 |
sheet.write(0, 12, "Our TP", heading_xf)
|
| 167 |
sheet.write(0, 12, "Our Offer Price", heading_xf)
|
168 |
sheet.write(0, 13, "Our Offer Price", heading_xf)
|
| 168 |
sheet.write(0, 13, "Our Rank", heading_xf)
|
169 |
sheet.write(0, 14, "Our Rank", heading_xf)
|
| 169 |
sheet.write(0, 14, "Lowest Seller", heading_xf)
|
170 |
sheet.write(0, 15, "Lowest Seller", heading_xf)
|
| 170 |
sheet.write(0, 15, "Lowest SP", heading_xf)
|
171 |
sheet.write(0, 16, "Lowest SP", heading_xf)
|
| 171 |
sheet.write(0, 16, "Lowest TP", heading_xf)
|
172 |
sheet.write(0, 17, "Lowest TP", heading_xf)
|
| 172 |
sheet.write(0, 17, "Lowest Offer Price", heading_xf)
|
173 |
sheet.write(0, 18, "Lowest Offer Price", heading_xf)
|
| 173 |
sheet.write(0, 18, "Inventory of Top Vendors", heading_xf)
|
174 |
sheet.write(0, 19, "Inventory of Top Vendors", heading_xf)
|
| 174 |
sheet.write(0, 19, "Our Snapdeal Inventory", heading_xf)
|
175 |
sheet.write(0, 20, "Our Snapdeal Inventory", heading_xf)
|
| 175 |
sheet.write(0, 20, "Our NLC", heading_xf)
|
176 |
sheet.write(0, 21, "Our NLC", heading_xf)
|
| 176 |
sheet.write(0, 21, "Lowest Possible TP", heading_xf)
|
177 |
sheet.write(0, 22, "Lowest Possible TP", heading_xf)
|
| 177 |
sheet.write(0, 22, "Lowest Possible SP", heading_xf)
|
178 |
sheet.write(0, 23, "Lowest Possible SP", heading_xf)
|
| 178 |
sheet.write(0, 23, "Competition Basis ", heading_xf)
|
179 |
sheet.write(0, 24, "Competition Basis ", heading_xf)
|
| 179 |
sheet.write(0, 24, "Can Compete", heading_xf)
|
180 |
sheet.write(0, 25, "Can Compete", heading_xf)
|
| 180 |
sheet.write(0, 25, "Target TP", heading_xf)
|
181 |
sheet.write(0, 26, "Target TP", heading_xf)
|
| 181 |
sheet.write(0, 26, "Target SP", heading_xf)
|
182 |
sheet.write(0, 27, "Target SP", heading_xf)
|
| 182 |
sheet.write(0, 27, "Target NLC", heading_xf)
|
183 |
sheet.write(0, 28, "Target NLC", heading_xf)
|
| 183 |
sheet.write(0, 28, "Sales Potential", heading_xf)
|
184 |
sheet.write(0, 29, "Sales Potential", heading_xf)
|
| 184 |
|
185 |
|
| 185 |
i, sheet_iterator=1,1
|
186 |
i, sheet_iterator=1,1
|
| 186 |
for one_line in all_lines:
|
187 |
for one_line in all_lines:
|
| 187 |
supc = all_supc[i-1]
|
188 |
supc = all_supc[i-1]
|
| 188 |
supc_data = fetchDetails(supc)
|
189 |
supc_data = fetchDetails(supc)
|
| Line 234... |
Line 235... |
| 234 |
sheet.write(sheet_iterator, 4, one_line.brand)
|
235 |
sheet.write(sheet_iterator, 4, one_line.brand)
|
| 235 |
sheet.write(sheet_iterator, 5, one_line.model_name)
|
236 |
sheet.write(sheet_iterator, 5, one_line.model_name)
|
| 236 |
sheet.write(sheet_iterator, 6, one_line.model_number)
|
237 |
sheet.write(sheet_iterator, 6, one_line.model_number)
|
| 237 |
sheet.write(sheet_iterator, 7, one_line.color)
|
238 |
sheet.write(sheet_iterator, 7, one_line.color)
|
| 238 |
sheet.write(sheet_iterator, 8, one_line.weight)
|
239 |
sheet.write(sheet_iterator, 8, one_line.weight)
|
| - |
|
240 |
sheet.write(sheet_iterator, 9, courierCost)
|
| 239 |
sheet.write(sheet_iterator, 9, one_line.risky)
|
241 |
sheet.write(sheet_iterator, 10, one_line.risky)
|
| 240 |
sheet.write(sheet_iterator, 10, supc_data.ourSp)
|
242 |
sheet.write(sheet_iterator, 11, supc_data.ourSp)
|
| 241 |
sheet.write(sheet_iterator, 11, round(supc_data.ourSp*0.9597-courierCost))
|
243 |
sheet.write(sheet_iterator, 12, round(supc_data.ourSp*0.9597-courierCost))
|
| 242 |
sheet.write(sheet_iterator, 12, supc_data.ourOfferPrice)
|
244 |
sheet.write(sheet_iterator, 13, supc_data.ourOfferPrice)
|
| 243 |
sheet.write(sheet_iterator, 13, supc_data.rank)
|
245 |
sheet.write(sheet_iterator, 14, supc_data.rank)
|
| 244 |
sheet.write(sheet_iterator, 14, supc_data.lowestSellerName)
|
246 |
sheet.write(sheet_iterator, 15, supc_data.lowestSellerName)
|
| 245 |
sheet.write(sheet_iterator, 15, supc_data.lowestSp)
|
247 |
sheet.write(sheet_iterator, 16, supc_data.lowestSp)
|
| 246 |
if one_line.parent_category ==10011:
|
248 |
if one_line.parent_category ==10011:
|
| 247 |
lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost
|
249 |
lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost
|
| 248 |
else:
|
250 |
else:
|
| 249 |
lowestTp = supc_data.lowestSp*0.9497-courierCost
|
251 |
lowestTp = supc_data.lowestSp*0.9497-courierCost
|
| 250 |
sheet.write(sheet_iterator, 16, round(lowestTp))
|
252 |
sheet.write(sheet_iterator, 17, round(lowestTp))
|
| 251 |
sheet.write(sheet_iterator, 17, supc_data.lowestOfferPrice)
|
253 |
sheet.write(sheet_iterator, 18, supc_data.lowestOfferPrice)
|
| 252 |
sheet.write(sheet_iterator, 18, supc_data.otherInventory)
|
254 |
sheet.write(sheet_iterator, 19, supc_data.otherInventory)
|
| 253 |
sheet.write(sheet_iterator, 19, supc_data.ourInventory)
|
255 |
sheet.write(sheet_iterator, 20, supc_data.ourInventory)
|
| 254 |
sheet.write(sheet_iterator, 20, one_line.our_nlc)
|
256 |
sheet.write(sheet_iterator, 21, one_line.our_nlc)
|
| 255 |
if supc_data.rank==1:
|
257 |
if supc_data.rank==1:
|
| 256 |
i+=1
|
258 |
i+=1
|
| 257 |
sheet_iterator+=1
|
259 |
sheet_iterator+=1
|
| 258 |
continue
|
260 |
continue
|
| 259 |
lowest_possible_tp = one_line.our_nlc/0.988+15+6
|
261 |
lowest_possible_tp = one_line.our_nlc/0.988+15+6
|
| 260 |
lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597
|
262 |
lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597
|
| 261 |
sheet.write(sheet_iterator, 21, round(lowest_possible_tp))
|
263 |
sheet.write(sheet_iterator, 22, round(lowest_possible_tp))
|
| 262 |
sheet.write(sheet_iterator, 22, round(lowest_possible_sp))
|
264 |
sheet.write(sheet_iterator, 23, round(lowest_possible_sp))
|
| 263 |
competitionBasis ='TP'
|
265 |
competitionBasis ='TP'
|
| 264 |
if (supc_data.lowestOfferPrice == supc_data.lowestSp):
|
266 |
if (supc_data.lowestOfferPrice == supc_data.lowestSp):
|
| 265 |
competitionBasis ='SP'
|
267 |
competitionBasis ='SP'
|
| 266 |
sheet.write(sheet_iterator, 23, 'SP')
|
268 |
sheet.write(sheet_iterator, 24, 'SP')
|
| 267 |
else:
|
269 |
else:
|
| 268 |
sheet.write(sheet_iterator, 23, 'TP')
|
270 |
sheet.write(sheet_iterator, 24, 'TP')
|
| 269 |
proposed_tp = 0
|
271 |
proposed_tp = 0
|
| 270 |
if lowestTp > lowest_possible_tp:
|
272 |
if lowestTp > lowest_possible_tp:
|
| 271 |
sheet.write(sheet_iterator, 24, "Yes")
|
273 |
sheet.write(sheet_iterator, 25, "Yes")
|
| 272 |
else:
|
274 |
else:
|
| 273 |
sheet.write(sheet_iterator, 24, "No")
|
275 |
sheet.write(sheet_iterator, 25, "No")
|
| 274 |
if competitionBasis=='SP':
|
276 |
if competitionBasis=='SP':
|
| 275 |
proposed_sp = max(supc_data.lowestSp - max((10, supc_data.lowestSp*0.001)), lowest_possible_sp)
|
277 |
proposed_sp = max(supc_data.lowestSp - max((10, supc_data.lowestSp*0.001)), lowest_possible_sp)
|
| 276 |
proposed_tp = proposed_sp*0.9597-courierCost
|
278 |
proposed_tp = proposed_sp*0.9597-courierCost
|
| 277 |
sheet.write(sheet_iterator, 25, round(proposed_tp))
|
279 |
sheet.write(sheet_iterator, 26, round(proposed_tp))
|
| 278 |
sheet.write(sheet_iterator, 26, round(proposed_sp))
|
280 |
sheet.write(sheet_iterator, 27, round(proposed_sp))
|
| 279 |
sheet.write(sheet_iterator, 27, round(proposed_tp*0.988+21))
|
281 |
sheet.write(sheet_iterator, 28, round(proposed_tp*0.988-21))
|
| 280 |
else:
|
282 |
else:
|
| 281 |
proposed_tp = max(lowestTp - max((10, lowestTp*0.001)), lowest_possible_tp)
|
283 |
proposed_tp = max(lowestTp - max((10, lowestTp*0.001)), lowest_possible_tp)
|
| 282 |
sheet.write(sheet_iterator, 25, round(proposed_tp))
|
284 |
sheet.write(sheet_iterator, 26, round(proposed_tp))
|
| 283 |
sheet.write(sheet_iterator, 26, round((proposed_tp+courierCost)/0.9597))
|
285 |
sheet.write(sheet_iterator, 27, round((proposed_tp+courierCost)/0.9597))
|
| 284 |
sheet.write(sheet_iterator, 27, round(proposed_tp*0.988-21))
|
286 |
sheet.write(sheet_iterator, 28, round(proposed_tp*0.988-21))
|
| 285 |
|
287 |
|
| 286 |
|
288 |
|
| 287 |
if (supc_data.lowestOfferPrice - one_line.our_nlc) < 0:
|
289 |
if (supc_data.lowestOfferPrice - one_line.our_nlc) < 0:
|
| 288 |
sheet.write(sheet_iterator, 28, "HIGH")
|
290 |
sheet.write(sheet_iterator, 29, "HIGH")
|
| 289 |
elif (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice >=0 and (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice <= .02:
|
291 |
elif (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice >=0 and (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice <= .02:
|
| 290 |
sheet.write(sheet_iterator, 28, "MEDIUM")
|
292 |
sheet.write(sheet_iterator, 29, "MEDIUM")
|
| 291 |
else:
|
293 |
else:
|
| 292 |
sheet.write(sheet_iterator, 28, "LOW")
|
294 |
sheet.write(sheet_iterator, 29, "LOW")
|
| 293 |
i= i+1
|
295 |
i= i+1
|
| 294 |
sheet_iterator+=1
|
296 |
sheet_iterator+=1
|
| 295 |
|
297 |
|
| 296 |
createSheetForBuyBoxItems(buyBoxItems,wbk)
|
298 |
createSheetForBuyBoxItems(buyBoxItems,wbk)
|
| 297 |
createSheetForCometitiveItems(competitive,wbk)
|
299 |
createSheetForCometitiveItems(competitive,wbk)
|
| Line 318... |
Line 320... |
| 318 |
sheet.write(0, 4, "Brand", heading_xf)
|
320 |
sheet.write(0, 4, "Brand", heading_xf)
|
| 319 |
sheet.write(0, 5, "Model Name", heading_xf)
|
321 |
sheet.write(0, 5, "Model Name", heading_xf)
|
| 320 |
sheet.write(0, 6, "Model Number", heading_xf)
|
322 |
sheet.write(0, 6, "Model Number", heading_xf)
|
| 321 |
sheet.write(0, 7, "Color", heading_xf)
|
323 |
sheet.write(0, 7, "Color", heading_xf)
|
| 322 |
sheet.write(0, 8, "Weight", heading_xf)
|
324 |
sheet.write(0, 8, "Weight", heading_xf)
|
| 323 |
sheet.write(0, 9, "Risky", heading_xf)
|
325 |
sheet.write(0, 9, "Courier Cost", heading_xf)
|
| 324 |
sheet.write(0, 10, "Our SP", heading_xf)
|
326 |
sheet.write(0, 10, "Risky", heading_xf)
|
| 325 |
sheet.write(0, 11, "Our TP", heading_xf)
|
327 |
sheet.write(0, 11, "Our SP", heading_xf)
|
| 326 |
sheet.write(0, 12, "Our Offer Price", heading_xf)
|
328 |
sheet.write(0, 12, "Our TP", heading_xf)
|
| 327 |
sheet.write(0, 13, "Our Rank", heading_xf)
|
329 |
sheet.write(0, 13, "Our Offer Price", heading_xf)
|
| 328 |
sheet.write(0, 14, "Lowest Seller", heading_xf)
|
330 |
sheet.write(0, 14, "Our Rank", heading_xf)
|
| 329 |
sheet.write(0, 15, "Second Lowest Seller", heading_xf)
|
331 |
sheet.write(0, 15, "Lowest Seller", heading_xf)
|
| 330 |
sheet.write(0, 16, "Second Lowest Price", heading_xf)
|
332 |
sheet.write(0, 16, "Second Lowest Seller", heading_xf)
|
| 331 |
sheet.write(0, 17, "Second Lowest Offer Price", heading_xf)
|
333 |
sheet.write(0, 17, "Second Lowest Price", heading_xf)
|
| 332 |
sheet.write(0, 18, "Second Lowest Seller TP", heading_xf)
|
334 |
sheet.write(0, 18, "Second Lowest Offer Price", heading_xf)
|
| 333 |
sheet.write(0, 19, "Second Lowest Seller SP", heading_xf)
|
335 |
sheet.write(0, 19, "Second Lowest Seller TP", heading_xf)
|
| 334 |
sheet.write(0, 20, "Our Snapdeal Inventory", heading_xf)
|
336 |
sheet.write(0, 20, "Our Snapdeal Inventory", heading_xf)
|
| 335 |
sheet.write(0, 21, "Second Lowest Seller Inventory", heading_xf)
|
337 |
sheet.write(0, 21, "Second Lowest Seller Inventory", heading_xf)
|
| 336 |
sheet.write(0, 22, "Our NLC", heading_xf)
|
338 |
sheet.write(0, 22, "Our NLC", heading_xf)
|
| 337 |
sheet.write(0, 23, "Competition Basis", heading_xf)
|
339 |
sheet.write(0, 23, "Competition Basis", heading_xf)
|
| 338 |
sheet.write(0, 24, "Target TP", heading_xf)
|
340 |
sheet.write(0, 24, "Target TP", heading_xf)
|
| Line 362... |
Line 364... |
| 362 |
sheet.write(i, 4, one_line.brand)
|
364 |
sheet.write(i, 4, one_line.brand)
|
| 363 |
sheet.write(i, 5, one_line.model_name)
|
365 |
sheet.write(i, 5, one_line.model_name)
|
| 364 |
sheet.write(i, 6, one_line.model_number)
|
366 |
sheet.write(i, 6, one_line.model_number)
|
| 365 |
sheet.write(i, 7, one_line.color)
|
367 |
sheet.write(i, 7, one_line.color)
|
| 366 |
sheet.write(i, 8, one_line.weight)
|
368 |
sheet.write(i, 8, one_line.weight)
|
| - |
|
369 |
sheet.write(i, 9, courierCost)
|
| 367 |
sheet.write(i, 9, one_line.risky)
|
370 |
sheet.write(i, 10, one_line.risky)
|
| 368 |
sheet.write(i, 10, supc_data.ourSp)
|
371 |
sheet.write(i, 11, supc_data.ourSp)
|
| 369 |
sheet.write(i, 11, round(supc_data.ourSp*0.9597-courierCost))
|
372 |
sheet.write(i, 12, round(supc_data.ourSp*0.9597-courierCost))
|
| 370 |
sheet.write(i, 12, supc_data.ourOfferPrice)
|
373 |
sheet.write(i, 13, supc_data.ourOfferPrice)
|
| 371 |
sheet.write(i, 13, supc_data.rank)
|
374 |
sheet.write(i, 14, supc_data.rank)
|
| 372 |
sheet.write(i, 14, supc_data.lowestSellerName)
|
375 |
sheet.write(i, 15, supc_data.lowestSellerName)
|
| 373 |
sheet.write(i, 15, supc_data.secondLowestSellerName)
|
376 |
sheet.write(i, 16, supc_data.secondLowestSellerName)
|
| 374 |
sheet.write(i, 16, supc_data.secondLowestSellerSp)
|
377 |
sheet.write(i, 17, supc_data.secondLowestSellerSp)
|
| 375 |
sheet.write(i, 17, supc_data.secondLowestSellerOfferPrice)
|
378 |
sheet.write(i, 18, supc_data.secondLowestSellerOfferPrice)
|
| 376 |
if one_line.parent_category ==10011:
|
379 |
if one_line.parent_category ==10011:
|
| 377 |
secondlowestTp = (supc_data.secondLowestSellerSp*(1-.0803))-courierCost
|
380 |
secondlowestTp = (supc_data.secondLowestSellerSp*(1-.0803))-courierCost
|
| 378 |
else:
|
381 |
else:
|
| 379 |
secondlowestTp = supc_data.secondLowestSellerSp*0.9497-courierCost
|
382 |
secondlowestTp = supc_data.secondLowestSellerSp*0.9497-courierCost
|
| 380 |
lowest_possible_tp = one_line.our_nlc/0.988+15+6
|
383 |
lowest_possible_tp = one_line.our_nlc/0.988+15+6
|
| 381 |
lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597
|
384 |
lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597
|
| 382 |
sheet.write(i, 18, secondlowestTp)
|
- |
|
| 383 |
sheet.write(i, 18, secondlowestTp)
|
385 |
sheet.write(i, 19, secondlowestTp)
|
| 384 |
sheet.write(i, 20, supc_data.ourInventory)
|
386 |
sheet.write(i, 20, supc_data.ourInventory)
|
| 385 |
sheet.write(i, 21, supc_data.secondLowestSellerInventory)
|
387 |
sheet.write(i, 21, supc_data.secondLowestSellerInventory)
|
| 386 |
sheet.write(i, 22, one_line.our_nlc)
|
388 |
sheet.write(i, 22, one_line.our_nlc)
|
| 387 |
|
389 |
|
| 388 |
competitionBasis ='TP'
|
390 |
competitionBasis ='TP'
|
| Line 395... |
Line 397... |
| 395 |
if competitionBasis=='SP':
|
397 |
if competitionBasis=='SP':
|
| 396 |
proposed_sp = max(supc_data.secondLowestSellerSp - max((20, supc_data.secondLowestSellerSp*0.002)), lowest_possible_sp)
|
398 |
proposed_sp = max(supc_data.secondLowestSellerSp - max((20, supc_data.secondLowestSellerSp*0.002)), lowest_possible_sp)
|
| 397 |
proposed_tp = proposed_sp*0.9597-courierCost
|
399 |
proposed_tp = proposed_sp*0.9597-courierCost
|
| 398 |
sheet.write(i, 24, round(proposed_tp))
|
400 |
sheet.write(i, 24, round(proposed_tp))
|
| 399 |
sheet.write(i, 25, round(proposed_sp))
|
401 |
sheet.write(i, 25, round(proposed_sp))
|
| 400 |
sheet.write(i, 26, round(proposed_tp*0.988+21))
|
402 |
sheet.write(i, 26, round(proposed_tp*0.988-21))
|
| 401 |
else:
|
403 |
else:
|
| 402 |
proposed_tp = max(secondlowestTp - max((20, secondlowestTp*0.002)), lowest_possible_tp)
|
404 |
proposed_tp = max(secondlowestTp - max((20, secondlowestTp*0.002)), lowest_possible_tp)
|
| 403 |
sheet.write(i, 24, round(proposed_tp))
|
405 |
sheet.write(i, 24, round(proposed_tp))
|
| 404 |
sheet.write(i, 25, round((proposed_tp+courierCost)/0.9597))
|
406 |
sheet.write(i, 25, round((proposed_tp+courierCost)/0.9597))
|
| 405 |
sheet.write(i, 26, round(proposed_tp*0.988-21))
|
407 |
sheet.write(i, 26, round(proposed_tp*0.988-21))
|
| Line 424... |
Line 426... |
| 424 |
sheet.write(0, 4, "Brand", heading_xf)
|
426 |
sheet.write(0, 4, "Brand", heading_xf)
|
| 425 |
sheet.write(0, 5, "Model Name", heading_xf)
|
427 |
sheet.write(0, 5, "Model Name", heading_xf)
|
| 426 |
sheet.write(0, 6, "Model Number", heading_xf)
|
428 |
sheet.write(0, 6, "Model Number", heading_xf)
|
| 427 |
sheet.write(0, 7, "Color", heading_xf)
|
429 |
sheet.write(0, 7, "Color", heading_xf)
|
| 428 |
sheet.write(0, 8, "Weight", heading_xf)
|
430 |
sheet.write(0, 8, "Weight", heading_xf)
|
| - |
|
431 |
sheet.write(0, 9, "Courier Cost", heading_xf)
|
| 429 |
sheet.write(0, 9, "Risky", heading_xf)
|
432 |
sheet.write(0, 10, "Risky", heading_xf)
|
| 430 |
sheet.write(0, 10, "Our SP", heading_xf)
|
433 |
sheet.write(0, 11, "Our SP", heading_xf)
|
| 431 |
sheet.write(0, 11, "Our TP", heading_xf)
|
434 |
sheet.write(0, 12, "Our TP", heading_xf)
|
| 432 |
sheet.write(0, 12, "Our Offer Price", heading_xf)
|
435 |
sheet.write(0, 13, "Our Offer Price", heading_xf)
|
| 433 |
sheet.write(0, 13, "Our Rank", heading_xf)
|
436 |
sheet.write(0, 14, "Our Rank", heading_xf)
|
| 434 |
sheet.write(0, 14, "Lowest Seller", heading_xf)
|
437 |
sheet.write(0, 15, "Lowest Seller", heading_xf)
|
| 435 |
sheet.write(0, 15, "Lowest SP", heading_xf)
|
438 |
sheet.write(0, 16, "Lowest SP", heading_xf)
|
| 436 |
sheet.write(0, 16, "Lowest TP", heading_xf)
|
439 |
sheet.write(0, 17, "Lowest TP", heading_xf)
|
| 437 |
sheet.write(0, 17, "Lowest Offer Price", heading_xf)
|
440 |
sheet.write(0, 18, "Lowest Offer Price", heading_xf)
|
| 438 |
sheet.write(0, 18, "Inventory of Top Vendors", heading_xf)
|
441 |
sheet.write(0, 19, "Inventory of Top Vendors", heading_xf)
|
| 439 |
sheet.write(0, 19, "Our Snapdeal Inventory", heading_xf)
|
442 |
sheet.write(0, 20, "Our Snapdeal Inventory", heading_xf)
|
| 440 |
sheet.write(0, 20, "Our NLC", heading_xf)
|
443 |
sheet.write(0, 21, "Our NLC", heading_xf)
|
| 441 |
sheet.write(0, 21, "Lowest Possible TP", heading_xf)
|
444 |
sheet.write(0, 22, "Lowest Possible TP", heading_xf)
|
| 442 |
sheet.write(0, 22, "Lowest Possible SP", heading_xf)
|
445 |
sheet.write(0, 23, "Lowest Possible SP", heading_xf)
|
| 443 |
sheet.write(0, 23, "Competition Basis ", heading_xf)
|
446 |
sheet.write(0, 24, "Competition Basis ", heading_xf)
|
| 444 |
sheet.write(0, 24, "Can Compete", heading_xf)
|
447 |
sheet.write(0, 25, "Can Compete", heading_xf)
|
| 445 |
sheet.write(0, 25, "Target TP", heading_xf)
|
448 |
sheet.write(0, 26, "Target TP", heading_xf)
|
| 446 |
sheet.write(0, 26, "Target SP", heading_xf)
|
449 |
sheet.write(0, 27, "Target SP", heading_xf)
|
| 447 |
sheet.write(0, 27, "Sales Potential", heading_xf)
|
450 |
sheet.write(0, 28, "Sales Potential", heading_xf)
|
| 448 |
|
451 |
|
| 449 |
i, sheet_iterator=1,1
|
452 |
i, sheet_iterator=1,1
|
| 450 |
for data in competitiveItems:
|
453 |
for data in competitiveItems:
|
| 451 |
supc_data =data[0]
|
454 |
supc_data =data[0]
|
| 452 |
one_line = data[1]
|
455 |
one_line = data[1]
|
| Line 468... |
Line 471... |
| 468 |
sheet.write(sheet_iterator, 4, one_line.brand)
|
471 |
sheet.write(sheet_iterator, 4, one_line.brand)
|
| 469 |
sheet.write(sheet_iterator, 5, one_line.model_name)
|
472 |
sheet.write(sheet_iterator, 5, one_line.model_name)
|
| 470 |
sheet.write(sheet_iterator, 6, one_line.model_number)
|
473 |
sheet.write(sheet_iterator, 6, one_line.model_number)
|
| 471 |
sheet.write(sheet_iterator, 7, one_line.color)
|
474 |
sheet.write(sheet_iterator, 7, one_line.color)
|
| 472 |
sheet.write(sheet_iterator, 8, one_line.weight)
|
475 |
sheet.write(sheet_iterator, 8, one_line.weight)
|
| - |
|
476 |
sheet.write(sheet_iterator, 9, courierCost)
|
| 473 |
sheet.write(sheet_iterator, 9, one_line.risky)
|
477 |
sheet.write(sheet_iterator, 10, one_line.risky)
|
| 474 |
sheet.write(sheet_iterator, 10, supc_data.ourSp)
|
478 |
sheet.write(sheet_iterator, 11, supc_data.ourSp)
|
| 475 |
sheet.write(sheet_iterator, 11, round(supc_data.ourSp*0.9597-courierCost))
|
479 |
sheet.write(sheet_iterator, 12, round(supc_data.ourSp*0.9597-courierCost))
|
| 476 |
sheet.write(sheet_iterator, 12, supc_data.ourOfferPrice)
|
480 |
sheet.write(sheet_iterator, 13, supc_data.ourOfferPrice)
|
| 477 |
sheet.write(sheet_iterator, 13, supc_data.rank)
|
481 |
sheet.write(sheet_iterator, 14, supc_data.rank)
|
| 478 |
sheet.write(sheet_iterator, 14, supc_data.lowestSellerName)
|
482 |
sheet.write(sheet_iterator, 15, supc_data.lowestSellerName)
|
| 479 |
sheet.write(sheet_iterator, 15, supc_data.lowestSp)
|
483 |
sheet.write(sheet_iterator, 16, supc_data.lowestSp)
|
| 480 |
if one_line.parent_category ==10011:
|
484 |
if one_line.parent_category ==10011:
|
| 481 |
lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost
|
485 |
lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost
|
| 482 |
else:
|
486 |
else:
|
| 483 |
lowestTp = supc_data.lowestSp*0.9497-courierCost
|
487 |
lowestTp = supc_data.lowestSp*0.9497-courierCost
|
| 484 |
sheet.write(sheet_iterator, 16, round(lowestTp))
|
488 |
sheet.write(sheet_iterator, 17, round(lowestTp))
|
| 485 |
sheet.write(sheet_iterator, 17, supc_data.lowestOfferPrice)
|
489 |
sheet.write(sheet_iterator, 18, supc_data.lowestOfferPrice)
|
| 486 |
sheet.write(sheet_iterator, 18, supc_data.otherInventory)
|
490 |
sheet.write(sheet_iterator, 19, supc_data.otherInventory)
|
| 487 |
sheet.write(sheet_iterator, 19, supc_data.ourInventory)
|
491 |
sheet.write(sheet_iterator, 20, supc_data.ourInventory)
|
| 488 |
sheet.write(sheet_iterator, 20, one_line.our_nlc)
|
492 |
sheet.write(sheet_iterator, 21, one_line.our_nlc)
|
| 489 |
if supc_data.rank==1:
|
493 |
if supc_data.rank==1:
|
| 490 |
i+=1
|
494 |
i+=1
|
| 491 |
sheet_iterator+=1
|
495 |
sheet_iterator+=1
|
| 492 |
continue
|
496 |
continue
|
| 493 |
lowest_possible_tp = one_line.our_nlc/0.988+15+6
|
497 |
lowest_possible_tp = one_line.our_nlc/0.988+15+6
|
| 494 |
lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597
|
498 |
lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597
|
| 495 |
sheet.write(sheet_iterator, 21, round(lowest_possible_tp))
|
499 |
sheet.write(sheet_iterator, 22, round(lowest_possible_tp))
|
| 496 |
sheet.write(sheet_iterator, 22, round(lowest_possible_sp))
|
500 |
sheet.write(sheet_iterator, 23, round(lowest_possible_sp))
|
| 497 |
competitionBasis ='TP'
|
501 |
competitionBasis ='TP'
|
| 498 |
if (supc_data.lowestOfferPrice == supc_data.lowestSp):
|
502 |
if (supc_data.lowestOfferPrice == supc_data.lowestSp):
|
| 499 |
competitionBasis ='SP'
|
503 |
competitionBasis ='SP'
|
| 500 |
sheet.write(sheet_iterator, 23, 'SP')
|
504 |
sheet.write(sheet_iterator, 24, 'SP')
|
| 501 |
else:
|
505 |
else:
|
| 502 |
sheet.write(sheet_iterator, 23, 'TP')
|
506 |
sheet.write(sheet_iterator, 24, 'TP')
|
| 503 |
proposed_tp = 0
|
507 |
proposed_tp = 0
|
| 504 |
if lowestTp > lowest_possible_tp:
|
508 |
if lowestTp > lowest_possible_tp:
|
| 505 |
sheet.write(sheet_iterator, 24, "Yes")
|
509 |
sheet.write(sheet_iterator, 25, "Yes")
|
| 506 |
if competitionBasis=='SP':
|
510 |
if competitionBasis=='SP':
|
| 507 |
proposed_sp = max(supc_data.lowestSp - max((10, supc_data.lowestSp*0.001)), lowest_possible_sp)
|
511 |
proposed_sp = max(supc_data.lowestSp - max((10, supc_data.lowestSp*0.001)), lowest_possible_sp)
|
| 508 |
proposed_tp = proposed_sp*.9597-courierCost
|
512 |
proposed_tp = proposed_sp*.9597-courierCost
|
| 509 |
sheet.write(sheet_iterator, 25, round(proposed_tp))
|
513 |
sheet.write(sheet_iterator, 26, round(proposed_tp))
|
| 510 |
sheet.write(sheet_iterator, 26, round(proposed_sp))
|
514 |
sheet.write(sheet_iterator, 27, round(proposed_sp))
|
| 511 |
else:
|
515 |
else:
|
| 512 |
proposed_tp = max(lowestTp - max((10, lowestTp*0.001)), lowest_possible_tp)
|
516 |
proposed_tp = max(lowestTp - max((10, lowestTp*0.001)), lowest_possible_tp)
|
| 513 |
sheet.write(sheet_iterator, 25, round(proposed_tp))
|
517 |
sheet.write(sheet_iterator, 26, round(proposed_tp))
|
| 514 |
sheet.write(sheet_iterator, 26, round((proposed_tp+courierCost)/0.9597))
|
518 |
sheet.write(sheet_iterator, 27, round((proposed_tp+courierCost)/0.9597))
|
| 515 |
else:
|
519 |
else:
|
| 516 |
sheet.write(sheet_iterator, 24, "No")
|
520 |
sheet.write(sheet_iterator, 25, "No")
|
| 517 |
|
521 |
|
| 518 |
if (supc_data.lowestOfferPrice - one_line.our_nlc) < 0:
|
522 |
if (supc_data.lowestOfferPrice - one_line.our_nlc) < 0:
|
| 519 |
sheet.write(sheet_iterator, 27, "HIGH")
|
523 |
sheet.write(sheet_iterator, 28, "HIGH")
|
| 520 |
elif (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice >=0 and (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice <= .02:
|
524 |
elif (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice >=0 and (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice <= .02:
|
| 521 |
sheet.write(sheet_iterator, 27, "MEDIUM")
|
525 |
sheet.write(sheet_iterator, 28, "MEDIUM")
|
| 522 |
else:
|
526 |
else:
|
| 523 |
sheet.write(sheet_iterator, 27, "LOW")
|
527 |
sheet.write(sheet_iterator, 28, "LOW")
|
| 524 |
i= i+1
|
528 |
i= i+1
|
| 525 |
sheet_iterator+=1
|
529 |
sheet_iterator+=1
|
| 526 |
|
530 |
|
| 527 |
|
531 |
|
| 528 |
def createSheetForCometitiveItemsNoInventory(competitiveNoInventoryItems,wbk):
|
532 |
def createSheetForCometitiveItemsNoInventory(competitiveNoInventoryItems,wbk):
|
| Line 541... |
Line 545... |
| 541 |
sheet.write(0, 4, "Brand", heading_xf)
|
545 |
sheet.write(0, 4, "Brand", heading_xf)
|
| 542 |
sheet.write(0, 5, "Model Name", heading_xf)
|
546 |
sheet.write(0, 5, "Model Name", heading_xf)
|
| 543 |
sheet.write(0, 6, "Model Number", heading_xf)
|
547 |
sheet.write(0, 6, "Model Number", heading_xf)
|
| 544 |
sheet.write(0, 7, "Color", heading_xf)
|
548 |
sheet.write(0, 7, "Color", heading_xf)
|
| 545 |
sheet.write(0, 8, "Weight", heading_xf)
|
549 |
sheet.write(0, 8, "Weight", heading_xf)
|
| - |
|
550 |
sheet.write(0, 9, "Courier Cost", heading_xf)
|
| 546 |
sheet.write(0, 9, "Risky", heading_xf)
|
551 |
sheet.write(0, 10, "Risky", heading_xf)
|
| 547 |
sheet.write(0, 10, "Our SP", heading_xf)
|
552 |
sheet.write(0, 11, "Our SP", heading_xf)
|
| 548 |
sheet.write(0, 11, "Our TP", heading_xf)
|
553 |
sheet.write(0, 12, "Our TP", heading_xf)
|
| 549 |
sheet.write(0, 12, "Our Offer Price", heading_xf)
|
554 |
sheet.write(0, 13, "Our Offer Price", heading_xf)
|
| 550 |
sheet.write(0, 13, "Our Rank", heading_xf)
|
555 |
sheet.write(0, 14, "Our Rank", heading_xf)
|
| 551 |
sheet.write(0, 14, "Lowest Seller", heading_xf)
|
556 |
sheet.write(0, 15, "Lowest Seller", heading_xf)
|
| 552 |
sheet.write(0, 15, "Lowest SP", heading_xf)
|
557 |
sheet.write(0, 16, "Lowest SP", heading_xf)
|
| 553 |
sheet.write(0, 16, "Lowest TP", heading_xf)
|
558 |
sheet.write(0, 17, "Lowest TP", heading_xf)
|
| 554 |
sheet.write(0, 17, "Lowest Offer Price", heading_xf)
|
559 |
sheet.write(0, 18, "Lowest Offer Price", heading_xf)
|
| 555 |
sheet.write(0, 18, "Inventory of Top Vendors", heading_xf)
|
560 |
sheet.write(0, 19, "Inventory of Top Vendors", heading_xf)
|
| 556 |
sheet.write(0, 19, "Our Snapdeal Inventory", heading_xf)
|
561 |
sheet.write(0, 20, "Our Snapdeal Inventory", heading_xf)
|
| 557 |
sheet.write(0, 20, "Our NLC", heading_xf)
|
562 |
sheet.write(0, 21, "Our NLC", heading_xf)
|
| 558 |
sheet.write(0, 21, "Lowest Possible TP", heading_xf)
|
563 |
sheet.write(0, 22, "Lowest Possible TP", heading_xf)
|
| 559 |
sheet.write(0, 22, "Lowest Possible SP", heading_xf)
|
564 |
sheet.write(0, 23, "Lowest Possible SP", heading_xf)
|
| 560 |
sheet.write(0, 23, "Competition Basis ", heading_xf)
|
565 |
sheet.write(0, 24, "Competition Basis ", heading_xf)
|
| 561 |
sheet.write(0, 24, "Can Compete", heading_xf)
|
566 |
sheet.write(0, 25, "Can Compete", heading_xf)
|
| 562 |
sheet.write(0, 25, "Target TP", heading_xf)
|
567 |
sheet.write(0, 26, "Target TP", heading_xf)
|
| 563 |
sheet.write(0, 26, "Target SP", heading_xf)
|
568 |
sheet.write(0, 27, "Target SP", heading_xf)
|
| 564 |
sheet.write(0, 27, "Sales Potential", heading_xf)
|
569 |
sheet.write(0, 28, "Sales Potential", heading_xf)
|
| 565 |
|
570 |
|
| 566 |
i, sheet_iterator=1,1
|
571 |
i, sheet_iterator=1,1
|
| 567 |
for data in competitiveNoInventoryItems:
|
572 |
for data in competitiveNoInventoryItems:
|
| 568 |
supc_data =data[0]
|
573 |
supc_data =data[0]
|
| 569 |
one_line = data[1]
|
574 |
one_line = data[1]
|
| Line 585... |
Line 590... |
| 585 |
sheet.write(sheet_iterator, 4, one_line.brand)
|
590 |
sheet.write(sheet_iterator, 4, one_line.brand)
|
| 586 |
sheet.write(sheet_iterator, 5, one_line.model_name)
|
591 |
sheet.write(sheet_iterator, 5, one_line.model_name)
|
| 587 |
sheet.write(sheet_iterator, 6, one_line.model_number)
|
592 |
sheet.write(sheet_iterator, 6, one_line.model_number)
|
| 588 |
sheet.write(sheet_iterator, 7, one_line.color)
|
593 |
sheet.write(sheet_iterator, 7, one_line.color)
|
| 589 |
sheet.write(sheet_iterator, 8, one_line.weight)
|
594 |
sheet.write(sheet_iterator, 8, one_line.weight)
|
| - |
|
595 |
sheet.write(sheet_iterator, 9, courierCost)
|
| 590 |
sheet.write(sheet_iterator, 9, one_line.risky)
|
596 |
sheet.write(sheet_iterator, 10, one_line.risky)
|
| 591 |
sheet.write(sheet_iterator, 10, supc_data.ourSp)
|
597 |
sheet.write(sheet_iterator, 11, supc_data.ourSp)
|
| 592 |
sheet.write(sheet_iterator, 11, '-')
|
598 |
sheet.write(sheet_iterator, 12, '-')
|
| 593 |
sheet.write(sheet_iterator, 12, supc_data.ourOfferPrice)
|
599 |
sheet.write(sheet_iterator, 13, supc_data.ourOfferPrice)
|
| 594 |
sheet.write(sheet_iterator, 13, supc_data.rank)
|
600 |
sheet.write(sheet_iterator, 14, supc_data.rank)
|
| 595 |
sheet.write(sheet_iterator, 14, supc_data.lowestSellerName)
|
601 |
sheet.write(sheet_iterator, 15, supc_data.lowestSellerName)
|
| 596 |
sheet.write(sheet_iterator, 15, supc_data.lowestSp)
|
602 |
sheet.write(sheet_iterator, 16, supc_data.lowestSp)
|
| 597 |
if one_line.parent_category ==10011:
|
603 |
if one_line.parent_category ==10011:
|
| 598 |
lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost
|
604 |
lowestTp = (supc_data.lowestSp*(1-.0803))-courierCost
|
| 599 |
else:
|
605 |
else:
|
| 600 |
lowestTp = supc_data.lowestSp*0.9497-courierCost
|
606 |
lowestTp = supc_data.lowestSp*0.9497-courierCost
|
| 601 |
sheet.write(sheet_iterator, 16, round(lowestTp))
|
607 |
sheet.write(sheet_iterator, 17, round(lowestTp))
|
| 602 |
sheet.write(sheet_iterator, 17, supc_data.lowestOfferPrice)
|
608 |
sheet.write(sheet_iterator, 18, supc_data.lowestOfferPrice)
|
| 603 |
sheet.write(sheet_iterator, 18, supc_data.otherInventory)
|
609 |
sheet.write(sheet_iterator, 19, supc_data.otherInventory)
|
| 604 |
sheet.write(sheet_iterator, 19, supc_data.ourInventory)
|
610 |
sheet.write(sheet_iterator, 20, supc_data.ourInventory)
|
| 605 |
sheet.write(sheet_iterator, 20, one_line.our_nlc)
|
611 |
sheet.write(sheet_iterator, 21, one_line.our_nlc)
|
| 606 |
if supc_data.rank==1:
|
612 |
if supc_data.rank==1:
|
| 607 |
i+=1
|
613 |
i+=1
|
| 608 |
sheet_iterator+=1
|
614 |
sheet_iterator+=1
|
| 609 |
continue
|
615 |
continue
|
| 610 |
lowest_possible_tp = one_line.our_nlc/0.988+15+6
|
616 |
lowest_possible_tp = one_line.our_nlc/0.988+15+6
|
| 611 |
lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597
|
617 |
lowest_possible_sp = (lowest_possible_tp+courierCost)/0.9597
|
| 612 |
sheet.write(sheet_iterator, 21, round(lowest_possible_tp))
|
618 |
sheet.write(sheet_iterator, 22, round(lowest_possible_tp))
|
| 613 |
sheet.write(sheet_iterator, 22, round(lowest_possible_sp))
|
619 |
sheet.write(sheet_iterator, 23, round(lowest_possible_sp))
|
| 614 |
competitionBasis ='TP'
|
620 |
competitionBasis ='TP'
|
| 615 |
if (supc_data.lowestOfferPrice == supc_data.lowestSp):
|
621 |
if (supc_data.lowestOfferPrice == supc_data.lowestSp):
|
| 616 |
competitionBasis ='SP'
|
622 |
competitionBasis ='SP'
|
| 617 |
sheet.write(sheet_iterator, 23, 'SP')
|
623 |
sheet.write(sheet_iterator, 24, 'SP')
|
| 618 |
else:
|
624 |
else:
|
| 619 |
sheet.write(sheet_iterator, 23, 'TP')
|
625 |
sheet.write(sheet_iterator, 24, 'TP')
|
| 620 |
proposed_tp = 0
|
626 |
proposed_tp = 0
|
| 621 |
if lowestTp > lowest_possible_tp:
|
627 |
if lowestTp > lowest_possible_tp:
|
| 622 |
sheet.write(sheet_iterator, 24, "Yes")
|
628 |
sheet.write(sheet_iterator, 25, "Yes")
|
| 623 |
if competitionBasis=='SP':
|
629 |
if competitionBasis=='SP':
|
| 624 |
proposed_sp = max(supc_data.lowestSp - max((10, supc_data.lowestSp*0.001)), lowest_possible_sp)
|
630 |
proposed_sp = max(supc_data.lowestSp - max((10, supc_data.lowestSp*0.001)), lowest_possible_sp)
|
| 625 |
proposed_tp = proposed_sp*.9597-courierCost
|
631 |
proposed_tp = proposed_sp*.9597-courierCost
|
| 626 |
sheet.write(sheet_iterator, 25, round(proposed_tp))
|
632 |
sheet.write(sheet_iterator, 26, round(proposed_tp))
|
| 627 |
sheet.write(sheet_iterator, 26, round(proposed_sp))
|
633 |
sheet.write(sheet_iterator, 27, round(proposed_sp))
|
| 628 |
else:
|
634 |
else:
|
| 629 |
proposed_tp = max(lowestTp - max((10, lowestTp*0.001)), lowest_possible_tp)
|
635 |
proposed_tp = max(lowestTp - max((10, lowestTp*0.001)), lowest_possible_tp)
|
| 630 |
sheet.write(sheet_iterator, 25, round(proposed_tp))
|
636 |
sheet.write(sheet_iterator, 26, round(proposed_tp))
|
| 631 |
sheet.write(sheet_iterator, 26, round((proposed_tp+courierCost)/0.9597))
|
637 |
sheet.write(sheet_iterator, 27, round((proposed_tp+courierCost)/0.9597))
|
| 632 |
else:
|
638 |
else:
|
| 633 |
sheet.write(sheet_iterator, 24, "No")
|
639 |
sheet.write(sheet_iterator, 25, "No")
|
| 634 |
|
640 |
|
| 635 |
if (supc_data.lowestOfferPrice - one_line.our_nlc) < 0:
|
641 |
if (supc_data.lowestOfferPrice - one_line.our_nlc) < 0:
|
| 636 |
sheet.write(sheet_iterator, 27, "HIGH")
|
642 |
sheet.write(sheet_iterator, 28, "HIGH")
|
| 637 |
elif (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice >=0 and (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice <= .02:
|
643 |
elif (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice >=0 and (supc_data.lowestOfferPrice - one_line.our_nlc)/supc_data.lowestOfferPrice <= .02:
|
| 638 |
sheet.write(sheet_iterator, 27, "MEDIUM")
|
644 |
sheet.write(sheet_iterator, 28, "MEDIUM")
|
| 639 |
else:
|
645 |
else:
|
| 640 |
sheet.write(sheet_iterator, 27, "LOW")
|
646 |
sheet.write(sheet_iterator, 28, "LOW")
|
| 641 |
i= i+1
|
647 |
i= i+1
|
| 642 |
sheet_iterator+=1
|
648 |
sheet_iterator+=1
|
| 643 |
|
649 |
|
| 644 |
|
650 |
|
| 645 |
|
651 |
|