UNIX Shell
[[Link]]
#!/bin/sh
Pause() {
read -p "Press [Enter] to continue..."
}
MainMenu() {
while true
do
clear
echo "================================================="
echo " |TRADING JOURNAL DATABASE SYSTEM|"
echo " |Main Menu - Select Desired Operation(s): |"
echo " |<CRTL-Z Anytime to Enter Interactive CMD Prompt>|"
echo "================================================="
echo " M) View Manual"
echo " "
echo " 1) Drop Tables"
echo " 2) Create Tables"
echo " 3) Populate Tables"
echo " 4) Query Tables"
echo " "
echo " E) End/Exit"
echo -n "Choose: "
read CHOICE
if [[ ${CHOICE} == "0" ]]
then
echo "Nothing Here"
elif [[ ${CHOICE} == "1" ]]
then
bash drop_tables.sh
Pause
elif [[ ${CHOICE} == "2" ]]
then
bash create_tables.sh
Pause
elif [[ ${CHOICE} == "3" ]]
then
bash populate_tables.sh
Pause
elif [[ ${CHOICE} == "4" ]]
then
bash [Link]
Pause
elif [[ ${CHOICE} == "E" ]] || [[ ${CHOICE} == "e" ]]
then
exit 0
fi
done
}
# Main Program
ProgramStart() {
while true
do
MainMenu
done
}
ProgramStart
exit
[create_tables.sh]
#!/bin/sh
export LD_LIBRARY_PATH=/home/yourusername/instantclient_12_1
username="p59nguye"
password="05013277"
sqlplus64
"$username/$password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
(Host=[Link])(Port=1521))(CONNECT_DATA=(SID=orcl)))"
< ./[Link]
[[Link]]
CREATE TABLE User (
User_ID NUMBER PRIMARY KEY,
First_Name VARCHAR2(50) NOT NULL,
Last_Name VARCHAR2(50) NOT NULL,
Email VARCHAR2(50) NOT NULL UNIQUE,
Encrypted_Password VARCHAR2(50) NOT NULL
);
CREATE TABLE Trade (
Trade_ID NUMBER PRIMARY KEY,
User_ID NUMBER NOT NULL,
Stock_Symbol VARCHAR2(10) NOT NULL,
Entry_Date DATE NOT NULL,
Exit_Date DATE,
Entry_Price NUMBER NOT NULL,
Exit_Price NUMBER,
Position VARCHAR2(10) NOT NULL,
Volume NUMBER NOT NULL,
Commission_Fee NUMBER NOT NULL,
FOREIGN KEY (User_ID) REFERENCES User(User_ID)
);
CREATE TABLE Stock (
Stock_Symbol VARCHAR2(10) PRIMARY KEY,
Company_Name VARCHAR2(50) NOT NULL,
Sector VARCHAR2(50) NOT NULL
);
CREATE TABLE Performance_Metrics (
Metrics_ID NUMBER PRIMARY KEY,
User_ID NUMBER NOT NULL,
ROI NUMBER NOT NULL,
Average_Profit_Loss NUMBER NOT NULL,
Win_Loss_Ratio NUMBER NOT NULL,
FOREIGN KEY (User_ID) REFERENCES User(User_ID)
);
CREATE TABLE Journal_Entry (
Journal_ID NUMBER PRIMARY KEY,
Trade_ID NUMBER NOT NULL,
Notes VARCHAR2(255),
Emotions VARCHAR2(50),
Strategy_Tag VARCHAR2(50),
Attachments BLOB, FOREIGN KEY (Trade_ID) REFERENCES
Trade(Trade_ID)
);
[populate_tables.sh]
#!/bin/sh
export LD_LIBRARY_PATH=/home/yourusername/instantclient_12_1
username="p59nguye"
password="05013277"
sqlplus64
"$username/$password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
(Host=[Link])(Port=1521))(CONNECT_DATA=(SID=orcl)))"
< ./[Link]
[[Link]]
INSERT INTO Users(User_ID, First_Name, Last_Name, Email,
Encrypted_Password) VALUES
(1, 'John', 'Doe', '[Link]@[Link]', 'password1'),
(2, 'Jane', 'Doe', '[Link]@[Link]', 'password2'),
(3, 'Alice', 'Brown', '[Link]@[Link]', 'password3'),
(4, 'Bob', 'Smith', '[Link]@[Link]', 'password4'),
(5, 'Charlie', 'Johnson', '[Link]@[Link]', 'password5'),
(6, 'Dave', 'Williams', '[Link]@[Link]', 'password6'),
(7, 'Eve', 'Jones', '[Link]@[Link]', 'password7'),
(8, 'Frank', 'Garcia', '[Link]@[Link]', 'password8'),
(9, 'Grace', 'Martinez', '[Link]@[Link]', 'password9'),
(10, 'Emily', 'Smith', '[Link]@[Link]', 'password10');
INSERT INTO Stock(Stock_Symbol, Company_Name, Sector) VALUES
('AAPL', 'Apple Inc.', 'Technology'),
('GOOGL', 'Google', 'Technology'),
('AMZN', 'Amazon', 'Retail'),
('MSFT', 'Microsoft', 'Technology'),
('TSLA', 'Tesla', 'Automotive'),
('FB', 'Facebook', 'Technology'),
('JPM', 'JPMorgan Chase', 'Finance'),
('V', 'Visa', 'Finance'),
('WMT', 'Walmart', 'Retail'),
('PG', 'Procter & Gamble', 'Consumer Goods');
INSERT INTO Trade(Trade_ID, User_ID, Stock_Symbol, Entry_Date,
Exit_Date, Entry_Price, Exit_Price, Position, Volume, Commission_Fee)
VALUES
(1, 1, 'AAPL', '2023-01-01', '2023-01-10', 150, 160, 'Long', 10, 5),
(2, 1, 'GOOGL', '2023-01-02', '2023-01-11', 2000, 2100, 'Long', 5, 10),
(3, 1, 'AMZN', '2023-01-03', '2023-01-12', 1800, 1850, 'Short', 8, 6),
(4, 2, 'MSFT', '2023-01-04', '2023-01-13', 200, 205, 'Long', 12, 4),
(5, 3, 'TSLA', '2023-01-05', '2023-01-14', 600, 650, 'Short', 15, 7),
(6, 3, 'FB', '2023-01-06', '2023-01-15', 300, 290, 'Long', 20, 8),
(7, 4, 'JPM', '2023-01-07', '2023-01-16', 100, 105, 'Short', 30, 9),
(8, 5, 'V', '2023-01-08', '2023-01-17', 180, 190, 'Long', 25, 3),
(9, 6, 'WMT', '2023-01-09', '2023-01-18', 120, 130, 'Short', 22, 2),
(10, 2, 'PG', '2023-01-10', '2023-01-19', 90, 95, 'Long', 33, 1);
INSERT INTO Performance_Metric(Metrics_ID, User_ID, ROI,
Average_Profit_Loss, Win_Loss_Ratio) VALUES
(1, 1, 0.1, 50, 0.8),
(2, 1, 0.2, 60, 0.7),
(3, 2, 0.3, 70, 0.6),
(4, 2, 0.4, 80, 0.5),
(5, 3, 0.5, 90, 0.9),
(6, 3, 0.6, 100, 0.4),
(7, 4, 0.7, 110, 0.3),
(8, 5, 0.8, 120, 0.2),
(9, 6, 0.9, 130, 0.1),
(10, 2, 0.5, 100, 0.9);
INSERT INTO Journal_Entry(Journal_ID, Trade_ID, Notes, Emotions,
Strategy_Tag, Attachments) VALUES
(1, 1, 'Good Trade', 'Calm', 'Breakout', '[Link]'),
(2, 2, 'Bad Trade', 'Nervous', 'Mean Reversion', '[Link]'),
(3, 3, 'Average Trade', 'Neutral', 'Momentum', '[Link]'),
(4, 4, 'Excellent Trade', 'Excited', 'Breakout', '[Link]'),
(5, 5, 'Poor Trade', 'Anxious', 'Scalping', '[Link]'),
(6, 6, 'Decent Trade', 'Content', 'Trend Following', '[Link]'),
(7, 7, 'Good Trade', 'Calm', 'Breakout', '[Link]'),
(8, 8, 'Bad Trade', 'Frustrated', 'Reversal', '[Link]'),
(9, 9, 'Okay Trade', 'Indifferent', 'Range Trading', '[Link]'),
(10, 10, 'Average Trade', 'Neutral', 'Momentum', '[Link]');
[drop_tables.sh]
#!/bin/sh
export LD_LIBRARY_PATH=/home/yourusername/instantclient_12_1
username="p59nguye"
password="05013277"
sqlplus64
"$username/$password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
(Host=[Link])(Port=1521))(CONNECT_DATA=(SID=orcl)))"
< ./[Link]
[[Link]]
DROP TABLE Journal_Entry;
DROP TABLE Performance_Metrics;
DROP TABLE Trade;
DROP TABLE Stock;
DROP TABLE User;
[[Link]]
#!/bin/sh
# Environment setup
export LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib
# Database credentials
username="p59nguye"
password="05013277"
# Execute SQL function
execute_sql() {
local query_file=$1
sqlplus64
"$username/$password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
(Host=[Link])(Port=1521))(CONNECT_DATA=(SID=orcl)))" <
$query_file
}
# Menu options
echo '1. Calculates the average ROI for each stock'
echo '2. Lists all users who have ONLY placed long trades'
echo '3. Lists all users who have ONLY placed short trades'
echo '4. Lists the top 5 most frequently traded stocks'
echo '5. Lists stocks that are above the average trading volume'
echo '6. Retrieve All Stock Information'
# Read user input
read -p "Choose an option: " input
# Associative array to map user input to query files
declare -A query_map
query_map[1]="[Link]"
query_map[2]="[Link]"
query_map[3]="[Link]"
query_map[4]="[Link]"
query_map[5]="[Link]"
# Execute corresponding SQL based on user input
if [[ ${query_map[$input]} ]]; then
execute_sql ${query_map[$input]}
else
echo "Invalid input."
fi
[[Link]]
SELECT Stock_Symbol, AVG(Exit_Price - Entry_Price) / Entry_Price AS
Average_ROI
FROM Trade
GROUP BY Stock_Symbol;
[[Link]]
SELECT DISTINCT User_ID
FROM Trade T1
WHERE NOT EXISTS (
SELECT 1
FROM Trade T2
WHERE T2.User_ID = T1.User_ID AND [Link] = 'short'
);
[[Link]]
SELECT DISTINCT User_ID
FROM Trade T1
WHERE NOT EXISTS (
SELECT 1
FROM Trade T2
WHERE T2.User_ID = T1.User_ID AND [Link] = 'long'
);
[[Link]]
SELECT Stock_Symbol, COUNT(*) AS Trade_Count
FROM Trade
GROUP BY Stock_Symbol
ORDER BY Trade_Count DESC
LIMIT 5;
[[Link]]
SELECT Stock_Symbol, AVG(Volume) AS Average_Volume
FROM Trade
GROUP BY Stock_Symbol
HAVING AVG(Volume) > (SELECT AVG(Volume) FROM Trade);
[[Link]]
SELECT * FROM Stock;