# converting excel to apex

I am trying to go through the exercise of converting an excel spreadsheet into apex and have stumbled across a few interesting tricks and tidbits.

To create a calculator for metered and un-metered services in a spreadsheet requires two workbooks. You can tab between the two and enter data into each spreadsheet. If something like a pricelist is entered into a unique spreadsheet, static references and dynamic calculations can be easily. For example, we can create a workbook for archive - metered storage services and a workbook for archive - unmetered services which will be blank since this is not a service that is offered. If we create a third workbook called pricelist, we can enter the pricing for archive services into the pricelist spreadsheet and reference it from the other sheets. For archive cloud services you need to answer four basic questions; how many months, how much you will start archiving, how much you will end up with, and how much do we expect to read back during that period. We should see the following as questions

How Many Months?cell F6
Initial Storage Capacitycell F7
Final Storage CapacityCell F8
Retrieval FactorCell F9

The cost will be calculated as

Storage Capacity((F8+F7+((F8-F7)/F6))*F6/2*price_of_archive_per_month)/F6((F8+F7+((F8-F7)/F6))*F6/2*price_of_archive_per_month)
Retrieval Cost(((F8+F7+((F8-F7)/F6)/2)*(F9/100))*price_of_archive_retrieval/F6(((F8+F7+((F8-F7)/F6)/2)*(F9/100))*price_of_archive_retrieval
Outbound Data Transfersumifs(table lookup, table lookup, ...)sumifs(table lookup, table lookup,...*F6

In Apex, this is done a little differently with a sequence of select statements and formatting statements to get the right answer
`select '   sub-part: ' || PRICELIST.PART_NUMBER ||    ' - Archive Storage Capacity           ' as Description,to_char(PRICELIST.PRICE*1000*(:FINAL_ARCHIVE+:INITIAL_ARCHIVE+((:FINAL_ARCHIVE-:INITIAL_ARCHIVE)/12))*12/2, '\$999,990') as PRICE  from PRICELIST PRICELIST  where PRICELIST.PART_NUMBER = 'B82623'UNIONselect '   sub-part: ' || PRICELIST.PART_NUMBER ||    ' - Archive Retrieval           ' as Description,to_char(PRICELIST.PRICE*1000*(:FINAL_ARCHIVE+:INITIAL_ARCHIVE+((:FINAL_ARCHIVE-:INITIAL_ARCHIVE)/12))*12/2*(:RETRIEVE_ARCHIVE/100), '\$999,990') as PRICE  from PRICELIST PRICELIST  where PRICELIST.PART_NUMBER = 'B82624'UNIONselect '   sub-part: ' || PRICELIST.PART_NUMBER ||    ' - Archive Deletes           ' as Description,to_char(PRICELIST.PRICE*1000*(:FINAL_ARCHIVE+:INITIAL_ARCHIVE+((:FINAL_ARCHIVE-:INITIAL_ARCHIVE)/12))*12/2*(:DELETE_ARCHIVE/100), '\$999,990') as PRICE  from PRICELIST PRICELIST  where PRICELIST.PART_NUMBER = 'B82629'UNIONselect '   sub-part: ' || PRICELIST.PART_NUMBER ||    ' - Archive Small Files           ' as Description,to_char(:SMALL_ARCHIVE, '\$999,990') as PRICE  from PRICELIST PRICELIST  where PRICELIST.PART_NUMBER = 'B82630'UNIONselect '   sub-part: ' || PRICELIST.PART_NUMBER ||    ' - Outbound Data Transfer           ' as Description,to_char(PRICELIST.PRICE*1000*(:FINAL_ARCHIVE+:INITIAL_ARCHIVE+((:FINAL_ARCHIVE-:INITIAL_ARCHIVE)/12))*12/2*(:RETRIEVE_ARCHIVE/100), '\$999,990') as PRICE  from PRICELIST PRICELIST  where PRICELIST.PART_NUMBER = '123456'UNIONselect'   Total:' as Description,to_char(sum(price), '\$999,990') as Price from (  select   PRICELIST.PRICE*1000*(:FINAL_ARCHIVE+:INITIAL_ARCHIVE+((:FINAL_ARCHIVE-:INITIAL_ARCHIVE)/12))*12/2 as price from PRICELIST     where pricelist.part_number = 'B82623'  UNION  select PRICELIST.PRICE*1000*(:FINAL_ARCHIVE+:INITIAL_ARCHIVE+((:FINAL_ARCHIVE-:INITIAL_ARCHIVE)/12))*12/2*(:RETRIEVE_ARCHIVE/100) as price from PRICELIST    where pricelist.part_number = 'B82624'  UNION  select PRICELIST.PRICE*1000*(:FINAL_ARCHIVE+:INITIAL_ARCHIVE+((:FINAL_ARCHIVE-:INITIAL_ARCHIVE)/12))*12/2*(:DELETE_ARCHIVE/100) as price from PRICELIST    where pricelist.part_number = 'B82629'  UNION  select PRICELIST.PRICE*1000*(:FINAL_ARCHIVE+:INITIAL_ARCHIVE+((:FINAL_ARCHIVE-:INITIAL_ARCHIVE)/12))*12/2*(:RETRIEVE_ARCHIVE/100) as price from PRICELIST    where pricelist.part_number = '123456'    );`

The variables :INITIAL_ARCHIVE replaces F7, :FINAL_ARCHIVE replaces F8, and :RETRIEVE_ARCHIVE replaces F9. Rather than referring to the pricelist spreadsheet, we enter the pricing information into a database and do a select statement with the part_number being the key for the lookup. This allows for a much more dynamic pricebook and allows us to update and add items without risk of breaking the spreadsheet linkages. We can also use REST apis to create and update pricing using an outside program to keep our price calculator up to date and current. Using a spreadsheet allows users to have out of date versions and there really is not any way of communicating to users who have downloaded the spreadsheet that there are updates unless we are all using the same document control system.

Note that we can do running totals by doing a sum from a select ... union statement. This allows us to compare two different services like Amazon Glacier and Oracle Archive easily on the same page. The only thing that we need to add is the cost of Glacier in the database and generate the select statements for each of the Glacier components. We can do this and use a REST api service nightly or weekly to verify the pricing of the services to keep the information up to date.

The select statements that we are use are relatively simple. The difficult part is the calculation and formatting out the output. For the bulk of the select statements we are passing in variables entered into a form and adding or multiplying values to get quantities of objects that cost money. We then look up the price from the database and print out dollar or quantity amounts of what needs to be ordered. The total calculation is probably the most complex because it uses a sum statement that takes the results of a grouping of select statements and reformats it into a dollar or quantity amount.

An example of the interfaces would look like