【Query Builder】Query over downloaded Bank Account Transactions

Your bank may provide transaction data in a CSV file. In such cases, Sharperlight can use that data to perform a cost-of-living analysis.
This time I use ANZ Online banking to download a csv file.

Download the transactions from your account with ANZ Internet Banking.
Choose **Microsoft Excel(CSV)** as `Software package`
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/4085dc18-0440-5bff-ff34-0177772aa3df.png)
Launch **Query Builder** from Sharperlight application menu.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/4306032c-c889-46cb-f336-a6723b15ddc9.png)
Select **Details Report** for `Mode` and choose **System** as `Product`.
Select **CSV File** for `Table`.
As soon as **CSV File** for `Table` is selected, the related filters are displayed.
Select the downloaded csv file for `Filename or URL link` filter.
Set **N** for Enable Header, this is because the csv file doesn't have header row.
The right after the csv file is selected, the Engine starts reading the file and gets fields to building the `Selection` tree view.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/686e3e5d-0b7d-ecc7-6253-4d844ec1078f.png)
The csv file doesn't have the header so the columns name in `Selection` pane say C1, C2, ...C8.
Let's output all columns by draging them one by one to Outputs pane, or selecting **Output All** from the right-click menu.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/8c7d46ad-1e86-f67f-574d-b62474f9a6a5.png)
Click `Preview` button to run the query. The Query should work fine.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/22d5e459-93ed-28fd-e2f4-b6e33a0e3aad.png)


Go back to Query definition by clicking `Back to Query` button.
Rename the outputs description as shown below. You can choose **Change Description** from the right-click menu.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/abf50b4d-32f7-f54e-2d94-7f7cd184613b.png)
Type new description for the column.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/82055f8c-ed4b-e8af-392b-be9eb8340e11.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d7f74346-f680-807d-012d-d8684ac1f90f.png)
Remove C4 to C8 as they don't need this time.

`Amount` column has the both of Dr and Cr so let's edit it.
Add an expression from the right-click menu.
Find the reference for the `Amount` column from the `Functions` tree view and set it to `Expression Formula` text area.
Use **IIF()** function to retunr **Cr** amount only. Don't forget to convert the amount to numeric value by using **Val()** function. 
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/b3b0e4c8-5c10-a689-9ef7-15cd1c408075.png)
Click `OK` to save the expression, and rename its description as **Cr**.
Add another expression and write **IIF()** function to get **Dr** amount only.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/95162ca6-dbf2-6e41-4a7b-067918b35752.png)
Click `OK` to save the expression, and rename its description as **Dr**.
Hide `Amount` column as not required to be visible.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/688bc967-dd31-ad8e-572d-b8ef43742564.png)
Please try to preview the result.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/2baf8596-bcdd-c72d-7950-87412de812de.png)
Did you find how easy you can pull data from a CSV file?

Sharperlight query and writeback functionality can be used to transfer such transactional data in csv files to database tables.
You can then create any Sharperlight report using the database tables.
You can't automate the task because the csv file download can't be automated, but you can create a read/write task in the scheduler.
Then you can manually run the task each time you download a csv file.

Thank you for reading this article.