Excel Data conversion

The process has two parts

  1. Date Entry
  2. Calculations

Note: Only the first image will contain the heading depicting the location of fields in the following images.

Data Entry: ALL IN CAPITAL LETTERS

Sample Image:

Format to enter the Customer Reference Numbers & Guarantor Reference Number:

  1. Reference no with space: If the reference number is found with visible space in the image between its characters or group of characters, please replace that with 1 space.
  1.  Reference no without space: If the reference number has no space between its characters, no space is required to be entered in excel. For eg, if the customer reference number is 123456ASDF, it should be entered in excel in a similar format without any spaces.
  2. Reference number with/without space and a line break: If any reference number (Customer or Guarantor) are found in two lines. Join the first line and the last line without any spaces and where the space is found replace it with 1 space. For eg, if the reference number is:

342 6 2636 5 4,

Format to enter Customer Name & Guarantor Name:

  1. If the given Customer's name is Ms.Kelly B Manfredi, it should be entered as MS.KELLY<1space>M<1space>MALDONADO.

Format to enter City:

  1. City name with one word: eg, California, should be entered as CALIFORNIA
  2. City names with two or more words: firstword<1space>2ndword<1space>, For eg, Dome Rock, should be entered as DOME<1space>ROCK<1space>
  3. City names with special characters: No spaces are needed for special characters.

Format to enter State:

State should be entered as per image: For eg, ca, should be entered as CA

Purchase value:

Purchase value given in the image as text format and should be entered in numeric value: For eg, “$Seventy Three Million One Hundred Ninety Five Thousand One Hundred Eighty Two Dollars   and   Forty One Cents”, should be entered as 73195182.41

Down Payment%:

Down Payment given in the image as text format and should be entered in numeric value: For eg, “Twenty Eight %”, should be entered as “28”

Loan Period:

Loan period given in the image as text format and should be entered in numeric value: For eg, “Eleven Years”, should be entered as “11”

Annual Interest %:

Annual Interest given in the image as text format and should be entered in numeric value: For eg, “Eight.Eight %”, should be entered as “8.8” (with decimal)

Purchase Value Reduction %:

Purchase Value Reduction given in the image as alpha numeric format and should be entered in numeric value: For eg, Purchase Value Reduction 20.28 %”, should be entered as “20.28” (with decimal)

Monthly Principal Reduction %:

Monthly Principal Reduction given in the image as alpha numeric format and should be entered in numeric value: For eg, Monthly Principal Reduction 4.69 %”, should be entered as “4.69” (with decimal)

Total Interest Reduction %:

Total Interest Reduction given in the image as alpha numeric format and should be entered in numeric value: For eg, Total Interest Reduction 16.74 %”, should be entered as “16.74” (with decimal)

Property Tax

  1. Offline search:
  1. If the city or the state is not present in the Assessment Rate excel sheet, an online search would be required.
  1. Once the city’s name is located, the following terms should be kept in mind:

An example for the Property Tax online search with Agoura, CA as the city and state
Agoura Hills        32.15%

This is found from the list, but we are searching for the Agoura and not Agoura Hills.

We need online search:

Google: https://www.google.co.in/?gfe_rd=cr&ei=u-5hVqrWLtiRvATfm7agBg&gws_rd=ssl#q=Agoura+California

Wiki: https://en.wikipedia.org/wiki/Agoura,_California

From the search, we find that Agoura is in Los Angeles County and is located southeast of the city of Agoura Hills, California, adjacent to the city of Calabasas in Los Angeles County

Agoura is located near Agoura Hills

Agoura is also located near to Calabasas

Therefore, we now have 3 details.

1. County details
2. Agoura Hills

3. Calabasas

Now let us find the County seat name for Los Angeles.

https://en.wikipedia.org/wiki/Los_Angeles_County,_California

Now we have:

Los Angeles County, also known as L.A. County
The county seat is the city of Los Angeles
County name and the county seat name is same.

Now let us start searching all the above details in the excel sheet.

1. County details
2. Agoura Hills 32.15%
3. Calabasas 40.14%

Note: While searching for the County name, even if the county’s name is repeated with many entries we only need to go for the highest rate determination just select the exact match only.

So we get:

County name: Los Angeles 35.16%
County seat name: Los Angeles: Chinatown (Los Angeles)        42.80%

Therefore, 42.80% is the highest rate now, Agoura Hills and Calabasas and Los Angeles (County name)