Excel 2007 : Automating stock portfolio workbook

I am maintaining a stock portfolio excel file. Basically the structure is each trading account is kept in separate worksheet and in each of these worksheet are the individual stocks in the trading account. Eg is account # 123 Open is stocks in open position (meaning I still hold shares in the stock); 123 Closed is closed position where I don't hold anymore shares. Buying new shares and the cost is added in and selling of shares is deduction & recorded as minus in "no. of shares" and sale proceeds as a minus in "Total amount". A summary of open position is in "Summary" worksheet by linking to the trading account worksheet & any closed position is summarised under closed positon in the Summary.

My problem is I have to manually record each buy or sell transaction in the appropriate account worksheet everytime there is a buy or sell stocks and my portfolio has more than 2 trading accounts and each account has more than 10 stocks. To add to complication is the current foreign exchange has to be applied to each transaction for the day. The current foreign exchange is in the yellow box in the summary sheet.

Any idea how I cut down the number of steps I have to make or is there a way i can restructure the workbook so that i only key in once & the worksheets will be automatically updated?