Spreadsheet Module

From ExtractValue Wiki

The Spreadsheet Module refers to the set of classes that allow for data input and output in the GUI form resembling spreadsheets. It will be connected to the Correlo Performance Module, and possibly other modules that require data input from users. The objective is to provide user friendly and visually appealing option of data entry.

The best researched choice is using JavaScript on the front-end (browser) to render and accept user interaction. Then the script communicates with back-end to store the information. TrimSpreadsheet is an open-source that can be extended for our purpose.

1 Data Input
2 Rules
3 Scenario
4 Technical Specifications
5 Rules
6 Category Tree

Data Input

For the purpose of this document, rows will be notated as "A,B,C..." and columns will be notated as "1,2,3..." with cells as "A1,B2,C3..."

Data should be able to be submit by user by (in order of desirability)

Copy-n-Paste
File upload
Manual entry

Data type should be determined by the header name. The mapping of column header to back-end data should be maintain by a configuration file/object which can be easily updated/changed. The header name should be case-insensitive, so if we specify a name - Value, it would be okay if the user put - value or VALUE. The system should recognize all variation as the same header name.

Manual Entry

User should be able to enter the data manually. The GUI/display should resemble that of an actual spreadsheet (a possible look). Data can be recorded using AJAX method as user complete each cell entry, or in one package at the final submit. Either way, no data should be lost, and all data should pass the basic validation.

Copy and Pasting

User should be able to copy-and-pasted directly from spreadsheet program such as Microsoft Excel worksheets, or some other document that they already have. The GUI/display should resemble that of an actual spreadsheet, and the visual layer should take the data and format it properly like this target look. After pasting, the data should display in the right cells for user to verify. Finally, the data should be send and saved on the back-end.

File Uploads

The Spreadsheet Module should support these file types. The GUI/display can be pretty standard. A button allowing user to 'Browser' their computer for local file, or a text box allowing them to type the file path manually. A button to submit the information. The JavaScript will parse the uploaded information and display the result in a spreadsheet view (sample look).

Rules

Headers

Row A = header
- The header will always determine what the data type is in the column under the header.
Row B = beginning of data
Row C...ZZ = more data

Dates

The module should be able to take all the common date format.

Excel stores dates and times as a number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day: ddddd.tttttt . This is called a serial date, or serial date-time. The integer portion of the number, tttttt, represents the fractional portion of a 24 hour day. For example, 6:00 AM is stored as 0.25, or 25% of a 24 hour day. Similarly, 6PM is stored at 0.75, or 75% percent of a 24 hour day.

More information can be found here

Correlo associated Header Names

**Abstract Data Fields and Types**
Date/time	Rate	Value	AUM
micro-timestamps	float	float	float

If all dates (Column 1) are consistently reported on a particular day of each month, such as the 1st day of each month, then on the confirmation page ask if the user would like to reformat the performance for the end of the month. If the user selects yes, then reformat the data to be 11:59:59.999 of the last day of each month.
If the above has not been performed and all times (Column 1) are reported on a particular time of each day, such as the 12:00:00.000 of each day, then on the confirmation page ask if the user would like to reformat the performance for the end of the day. If the user selects yes, then reformat the data to be 11:59:59.999 of each day reported.
If all rate of returns (Column 2) have a % next to the number, then assume that the data is in percentages.

Scenario

Please see the following wireframes. Additionally, please test out the Swivel system, which also uses Google Spreadsheets/Data API.

Figure 1.0: Empty Sheet

Figure 1.1: Sample Sheet

Figure 1.2: Upload data

Manual entry
1. A user will be on the data entry page
2. The user sees a spreadsheet visual as the entry "form" (Figure 1.0)
3. The user enter rows of data according to the header names (Figure 1.1)
4. The user click on the submit button to complete the data entry process
5. All data is stored properly on the back-end, and the user receives notificationn of "Data Saved" and redirect to some page
Copy-n-Paste entry
1. A user arrives and sees the same thing as in the manual entry
2. The user attempt to copy a block of data from Excel spreadsheet, or other documents
3. The user click on the form to active the form
4. The user paste the data into the form, and it is format properly with header and rows of data, please check Google Spreadsheet for a sample experience
5. The user edit the data as needed
6. The user click on the submit button to complete the data entry process
7. All data is stored properly on the back-end, and the user receives notificationn of "Data Saved" and redirect to some page
File upload
1. A user arrives and sees the same thing as in the manual entry
2. The user sees the extra input method below the spreadsheet indicating the ability to upload a file (Figure 1.2)
3. The user will either enter the path into the text field, or click on the "browse" button next to the field to browse on local computer for the desired file
4. The user click on a button below the text field to submit and upload the file
5. The data will be parsed, and stored properly on the back-end
6. The user receives notificationn of "Data Saved" and redirect to some page... or some "Error" message indicate upload problem

Technical Specifications

For All

The application will be cross-browsers compatible. It will support at minimum firefox 2/3, safari, and Internet Explorer 6/7/8.
Create a GUI for user to enter/update records of data.
Date/time value should be smartly parsed. The application will recognize and translate common date formats into micro Unix timestamps. Google Spreadsheets API or Google Data API may be able to do this.
There will be a button to indicate the end of entry or final submission/save
Application should present to user with message to indicate status at the end.
All work must follow the MVC framework
The back-end code in PHP will use codeigniter/kohana framework
The view layer should be responsible for generating the HTML layout, and any Javascript function (AJAX)
The controller layer will respond to AJAX request, and validate passed-in data before processing
The model layer will store the data into database with standard SQL statement(s)
The inputted data will be formatted to:

array(<micro-timestamps> =>

array(date => <micro-timestamps>,

rate => <float or null>,

value => <float or null>,

asset => <float or null>

)

Selections During Input of Data

Application should allow pre-populating of the displaying data for user to edit existing data
1. Allow pre-populating of data during application initiation
2. Allow for receiving data from back-end and displaying them properly on the form ( Optional )
Page should contain a configuration fields area which indicate the kind of data the user is import
1. Drop-down box for deliminator:
  - comma
  - semicolon
  - tab (default)
  - pipe
2. Text field for text/value deliminator ( default is empty )
3. Radio button for "Do columns have titles" ( default is NO )
4. Drop-down box to choose the column data type
  - auto (default)
  - date/time
  - number
  - percentage
  - currency
  - text

Spreadsheet

Instead of a standard entry form which represent a single record, and forcing user to go through numerous forms in order to enter all records, the website will have an AJAX enabled component which resembles a spreadsheet. User can choose to either copy-n-paste, or manually enter the data.
1. No formatting (bold, italicize, underline, left justify, center justify, right justify)
The view should be re-sizable
The entry field should be a text-area instead of text field to accommodate a large data copy-n-pasted in by the user
When receiving copy-n-paste data, the JS script needs to parse the data on the spot, or use Google API (whichever is easiest, look into Google Spreadsheets API first)
When using the Google Spreadsheets API, data should be deleted after being stored in the application database

File Upload

Text field for user to manually input the file name
1. Alternative button for user to browse the local computer for the file
The script will use Google Spreadsheets API and the configuration information to properly parse the given file data ( if better parsing is possible, please use that)
When using the Google Spreadsheets API, data will be delete on Google's database for security
The parsed data is displayed in the spreadsheet visual to allow final editing by the user

Rules

Date/time

If the date appears as Jan 1980, timestamp the date/time as the last moment of the month. For example, Jan 1980 = January 31, 1980 at 11:59:59.999
If the date appears as Jan 12, 1980, timestamp the date/time as the last moment of that day. For example, Jan 12, 1980 = January 12, 1980 at 11:59:59.999
The confirmation page will not have date/time changeable

AUM

If the AUM is less than $50,000, then assume that the AUM is in Thousands.
If the AUM is greater than $100 billion, then give an error message stating "You have inputted an invalid number for Assets Under Management. Please check your entry and try again."