Using Excel RTD to import data into Access

[mikem]

I have an Excel spreadsheet that I use to track stock trades and am able to use the Excel RTD function to get live prices from my ThinkOrSwim platform
I need to switch to Access to better manage the data in my trades.
Any ideas how I can use the Excel functionality to update the fields in my Access database?
In a perfect world, I would have a CurrentPrice field that auto updated using RTD. Then when I enter a date in to a CloseDate filed (after I close the trade) it would stop updating.
Any help is appreciated
thanks
mike

[alansidman]

Have you considered building your table in Excel and linking that table to Access. Each time your Excel table is updated, it will automatically update to Access.

https://www.youtube.com/watch?v=oiLMfKyavEU

[mikem]

thanks. that worked.
but i had to create a 2 way link.
first i add a new stock to Access
then the spreadsheet updates with the new stock
then the database updates with the price feed.

they made it so easy in excel, i was hoping for something similar in access

[somemale]

Hey Mike. I was wondering if you made any progress in your attempts to query RTD TOS data without the 2-way link with Excel.
TIA.

[jestinesari]

You can link your Excel sheet (with live stock prices from RTD) to Access and automatically update the CurrentPrice field for open trades (where CloseDate is blank). Here’s how:

1. Link Excel to Access: Use Access’s "External Data" feature to link your Excel sheet containing live prices.
2. Update Prices in Access: Create an Access query that updates CurrentPrice for open trades (CloseDate IS NULL).
   
   • Example: UPDATE Trades SET Trades.CurrentPrice = [ExcelLinkedTable].[CurrentPrice] WHERE Trades.CloseDate IS NULL;
   
   
3. Stop Updating When Closed: Once you enter a CloseDate, the query will stop updating that trade’s price.

You can also use VBA for more control, but the query method is simpler. Let me know if you need more help!

[CJ_London]

that looks so much like an AI response

[somemale]

Precisely. You know what I was asking. I find the 2-way handshake to be flaky at times and prefer to work entirely in Access to preserve history.