Scribd
Upload
14 views4 pages

Input Output Operations in Algorithmic Finance

This document outlines a tutorial on Input Output operations in Algorithmic Finance, focusing on loading, saving, and reading various data formats using pandas. It includes tasks such as saving a DataFrame to multiple file types, reading them back, and ensuring data integrity, as well as working with SQL databases for trade data management. Additionally, it prompts discussions on real-time trade data storage and database structuring.

Uploaded by

Maurice Wong
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
14 views4 pages

Input Output Operations in Algorithmic Finance

This document outlines a tutorial on Input Output operations in Algorithmic Finance, focusing on loading, saving, and reading various data formats using pandas. It includes tasks such as saving a DataFrame to multiple file types, reading them back, and ensuring data integrity, as well as working with SQL databases for trade data management. Additionally, it prompts discussions on real-time trade data storage and database structuring.

Uploaded by

Maurice Wong
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

5QQMN534: Algorithmic Finance

Tutorial 7 – Input Output (IO) Operations


a) Set working directory and path for reading and writing.
b) Load yahoo_data.xlsx. Round all columns to 4 decimal
places. Save as a DataFrame variable called original_data.
Question1: Saving Data Formats
c) Write the yahoo original_data DataFrame to the various
file types. Hint: you use pandas to_csv, to_excel,
to_pickle, to_sql
i. .csv excel file
Folder Output after writing files
ii. .xlsx excel file
iii. .pkl pickle file
iv. .db SQL Database file.
v. .h5 file
vi. .npy NumPy array
vii. .npz compressed NumPy array
d) Read in these file types back into new DataFrames. Make
sure the read data is identical to the layout in the
original_data DataFrame. Make any adjustments as
required. Remember to close any connections. For
instance :-
i. .csv file read in
ii. .xlsx file read in (round to 4 dp)
iii. .pkl file read in
iv. .db SQL Datbase file read in
v. .h5 file using pandas read in
vi. .npy numpy array read in (convert back to a
DataFrame, set columns and index)
e) Check each re_read DataFrames are the same as the original_data
vii. .npz compressed NumPy array read in (convert
DataFrame structure and layout.
back to a DataFrame, set columns and index)
f) What observations about the different file types and file sizes you can make? 2
Make a brief comment.
Question2: SQL Databases1 This resource will assist in this exercise:
a. Set directory [Link]
b. Import sqllite3 library
c. Create a new database connection db file called
[Link].
d. Create a cursor
e. Drop the Trades table if already exists
f. Create and execute a new table called Trades with
four columns direction (text), ticker (text),
name(text) and price (integer)
g. Using the trade_data_2 data provided and INSERT
this data into the SQL table in a loop process
formatting a unique SQL string command, execute
and commit.
h. Check the INSERT execution has written the data to
the SQL table and print info to screen show all
entries. Hint use fetchall()
i. Check and print info to screen show one SQL table
entry only. Hint use fetchone()
j. Use a SELECT statement to print out only the Long
trades
k. Use a SELECT statement to print out only the
ordered table by direction

3
Question2: SQL Databases2 Continued
l) Use a select statement to print out table results if
price > 300
m) Now delete all trades from table where direction is
long
n) Print out the table and check if deletion from the
Trades table has occurred correctly
o) Update the price for MSFT Short Trade to 104.22
p) Print out the table and check the amendment has
been made correctly
q) Close the SQL connection
r) Discuss and comment on below.
i. What additional columns would we normally have
if completing trades in real time and directly
storing to a database file?
ii. Would we want separate tables for running a
trading system and why?
iii. What tables would we potentially create?

You might also like