Workbook for Stock Trading

[jrtaylor]

I've worked with Excel for a long time, but my data has almost always been "flat." I want to create a kind of trading workbook though, and I have a feeling I'm going to need to use VBA.

This is what I envision: A "price table" which has a first row connected to an API or DB which provides current prices etc. The table would be about 30 rows deep. Let's say that the data feed row is #2.

What I want to do is have the data in row 2 move down to row 3 every time row 2 updates. And of course, the old row3 moves down to row 4... until we reach the last row (say 32). At that point the oldest data falls off the table, as it is no longer needed.

Assuming I can get the database/price feed working, what would be the best approach for updating the table in the fashion I describe above? It's fine with me if we put a timer in the solution, so that the updates are every 15 minutes, rather than every time the dynamic first row updates.

Other parts of the workbook would read the table and perform various calculations to assist with trading decisions.

So I'll be interested in your advice here.

Thanks!

[ranman256]

you want a non flat,relational database like Microsoft Access. Its best for importing large volumes of data and analyzing.
Import the records with no need to 'move rows down'.

[jrtaylor]

Thank you Ranman. Here's the challenge for me:

If Row 2 = the latest dataset, and the table is 30 rows deep, then I need Excel to perform a number of complex functions on rows 2 through 30... AND perform functions on data from a second database which also updates periodically, and then use all of these calculations to generate a score to open or close trades.

The first dataset comes in through the stock broker's api, and the second dataset comes in via amazon web services.

So if I set up Access to receive the data, could I then connect Excel to Access and have my calculations run every x minutes?

Thanks!

[jrtaylor]

Discovered that Excel 1711 (or maybe earlier versions?) have capacity to do this sorting by editing the query.