Detecting Price and Volume Patterns with SQL and TimescaleDB

[@parttimelarry]

I'm starting a new channel on AI at youtube.com/@parttimeai, please subscribe! Source Code: github.com/hackingthemarkets/timescaledb-aiohttp-asyncpg

[@AppFanUAE]

Thank you very much wow amazing

[@Kate-ud2xc]

how interesting and informative! :) Thank you so much for the vedio!

[@sakthiprasad6008]

Nice videos. Thanks for the intro to timescale DB. It was new to me. Previously i was using rank function to identify open and close values of the bar. with stock1 as (select *,rank() over (partition by symbol order by datetime asc) rnk1, --to_timestamp(cast(datetime/1000 as bigint)) at time zone 'america/new_york' CODAT, to_timestamp(floor(datetime/300000)*300) at time zone 'america/new_york' itvl from minutetable where 1=1 -- and to_timestamp(cast(datetime/1000 as bigint))::date in ('2020-01-07','2020-01-08') --And symbol in ('AAPL') order by symbol,datetime asc ) select a.symbol,b.open,c.close,a.mh high,a.ml low,a.vol volume,a.itvl datetime from (select min(rnk1) openrnk,max(rnk1) closernk,sum(volume) vol,symbol,max(high) mh,min(low) ml,itvl from stock1 group by symbol,itvl)a join (select open,symbol,itvl,rnk1,datetime from stock1 )b on a.symbol=b.symbol and a.openrnk=b.rnk1 join (select close,symbol,itvl,rnk1,datetime from stock1 )c on a.symbol=c.symbol and a.closernk=c.rnk1 order by symbol,a.itvl asc

[@cetilly]

Fantastic vid. Especially since I have a preference for putting all of the data logic in SQL, so it was good to see you introduce that. Also, you are an insanely fast typer.

[@edwardhasekamp3104]

thank you! my knowledge grows with every video. I love the TimeScaleDB!

[@anmolpreet8959]

Hey bud, Keep up the great works. You are awesome

[@seans9168]

This was the video I’ve been waiting for. Very nice examples, and great showcase of TSDB. Good call on switching to TimeScaleDB! This is the first time I’ve used Postgres and I have to say I’m really enjoying this RDBMS as a whole. After testing/using it for over a week now, I’m hooked!

[@x.e.b.u]

Really enjoyed the video. Thank you for the video. at 38:25 did bring a smile to my face. I'd invested in the stock (Silvergate) back in November. It has been on a huge run courtesy of crypto probably. "Whisky coming your way"

[@yourpersonaldatadealer2239]

I spent all day figuring this query out so expect it might help some others: -- Returns the latest two dates of data for all holdings (with two or more -- days' worth of data) in each ETF -- -- This ignores any newly added holdings for each ETF as these are handled -- separately and impair the date-paired analysis that is calculated via -- row-pairs in the Pandas Python library on the results of this query -- -- If you are wondering why we don't just use two dataframes (one from -- today and one from the previous day) and compare them as element-wise -- matrices, this is because holdings can move up or down in ETFs. This -- would cause anomalies within the analysis and so -- pairs were used to counteract this issue SELECT etf_id, holding_id, date, company, ticker, shares, market_value, weight_percentage FROM ( SELECT *, -- Count the number of rows for each combination row_number() over ( PARTITION BY etf_id, holding_id ORDER BY date DESC ) AS row_num FROM etf_holding WHERE (etf_id, holding_id) IN ( -- Select all holdings that have more than one days' worth of data as a subquery -- to filter out any new companies (identify added/removed companies in seperate query) SELECT etf_id, holding_id FROM etf_holding GROUP BY etf_id, holding_id HAVING count(*) > 1 ) ) AS temp_table WHERE -- Only return the latest two dates for each holding in each ETF row_num < 3;

[@iamvazu2768]

Thank you larry, great video again

[@kazimali07]

Your so smart :( I watching and learning so much!

[@ragecagegroves]

Awesome vid PTL!

[@sakthiprasad6008]

Also to find the out the SMAs we can use the below query select symbol,sma200,sma100,sma50,datetime from (select symbol,datetime,close, AVG(close) OVER (partition by symbol ORDER BY datetime asc ROWS 199 PRECEDING) AS sMA200, AVG(close) OVER (partition by symbol ORDER BY datetime asc ROWS 99 PRECEDING) AS sMA100, AVG(close) OVER (partition by symbol ORDER BY datetime asc ROWS 49 PRECEDING) AS sMA50 from (select symbol,open,close,high,low,volume,to_timestamp(cast(datetime/1000 as bigint))::date datetime FROM dailytable)b )a where --datetime='2020-07-21' and symbol='SPY'

[@theSoumyachatterjee]

This is really great! Thank you for this! Could you please help share your thoughts around identifying support and resistance levels for a particular stock algorithmically?

[@IDRC3000]

Really helpful and interesting topic! I will be awesome if you do a video talking about the books that you have read and recommend, also, how would you implement a NoSQL database for stock data analysis?

[@ChopStixLoL]

I'm predicting a breakout of views on this channel.

[@paultenzer1361]

Me too. I think it’s hilarious how many people throw all their money away “trading” with the gurus. No one wants to put in the work, coding your own strategy’s is the only way to really understand trading.

[@ballyoracle]

another awesome video .. made my SAT :) .. i was wondering how to do live stream ... for these pattern .. while markets are open and send a buy and sell

[@parttimelarry]

See the videos where I use websockets for intraday calculations in a dataframe, there are some examples where I demonstrate this with crypto examples and no database. Also, if you want to use a database like this, you could have code that executes on INSERT (perhaps as a stored procedure), so that when a new bar of data is inserted, code runs to see if that bar triggered whatever signal you are detecting. Or in the full stack tutorial, I fetched price data every minute with a cron job, then ran my calculations in Python in this scheduled job and places trades then.

[@ballyoracle]

@@parttimelarry I will work on it .. I am catching up on ll this awesome content .... every day.... today straight 6 hours now .. my head is exploding with possibilities.. BTW .. i am not able to load the price table populate_prices.py ... throwing me error " --- raise RuntimeError( RuntimeError: asyncio.run() cannot be called from a running event loop "

[@ballyoracle]

@@parttimelarry Thank you Larry ! you are more than awesome .. thank you thank you thank you

[@ewaschenko]

Do you know how we can link two different timescales together? Lets say pull our data on 5 min candles? But also want a 30 min bucket. When we are iterating through our 5 min candles, how do we know which 30 min bucket to use? For example 9:35, there will be buckets 9:30 and 10:00. the 9:35 candles maps to the 9:30 bucket, but is there a way to get that mapping?

[@raaapt0r]

Hey dude amazing content, I noticed the full stack app on your website doesn't go past the fifth part, any reason? Could you share it? :)

[@FinGeek4now]

Morning PTL, I was just wondering if you could do a video on using Google's Colab (which is what I use currently - its a cloud Jupyter notebook) as well as implementing Firestore (Firebase/NoSQL) within it?

[@Norvieable]

Dawg!!! You awesome

[@AppFanUAE]

You are amazing 👍🏼 can you please guide me from where i get the codes?

[@codya326]

Hey how is it going Larry , I have been watching so many of your videos and havnt quite seen what ive been looking for but i have learned so much in my time doing so. I was wondering if there was any way to make a bot that put out signals on stock that might break out based off 2 or 3 factors like for example if the rsi is over sold and the macd is crossing then a notification will be sent over in option form to discord where a discord bot will post a specific call or put option for that particular stock. I appreciate any help and the videos man keep it up !

[@Amit-et3ob]

Great work mate.. Please add more Crypto content. Stocks seems sooo boomer now.

[@StephenNavazio]

Oddly, Boomers know timeseries is just that. We appreciate any timeseries topics, Thanks PT Larry.

[@erfanmallakin9596]

Hi Larry, I admire your knowledge, I have a strategy which cannot be written in pine script at the moment ( due to script restrictions) how I can get help to implement it in other languages 😀?

[@oferz123]

Hi, I have a column named 'sma'. How I update the sma values with the output from the moving average window function results ?

[@TuranInsight]

Given this context- lead() should be used with previous_value, while lag() is for next_value right? or does it change from situation to situation

[@TuranInsight]

just found my answer: It all depends on ONDER BY DAY DESC/ASC

[@ohadkoren317]

Hey larry I got a question. I want my materialized views to be in sync with binance clock... which means that I want to have correlated intervals with binance. Do you know how can I achieve that with timescaledb?

[@symosys]

Which IDE are you using for postgreSQL?

[@parttimelarry]

If you mean the database browser, I am using TablePlus. You can also check out pgAdmin4 if you prefer a web-based user interface.

[@filipmunoz]

Hello Part time Larry! I'm working on a crypto currency python trader and it's doing great. But I have one issue, the script trades about 100 times per 24h. In Binance trading fees this is very expensive. Do you know of any other trading platforms where it is possible to pay monthly fees instead, or another way to reduce API trading fees?

[@jameswang6714]

@larry Where is Buy me coffee link?

[@nvlucho]

Are you more profitable as a trader or developer? Love your videos!

[@CarlosSilombria]

Excellent content mate. Do you (or anyone here) know who offers real-time data and/or historical (at least 1 min) of futures? I had a look to polygon,alpha vantage, twelvedata and as far as I check they don't offer futures. I'm looking for CME products: NQ (or NQ=F on yahoo, NQ1! on tradingview) RTY, ES (SPY500) etc. Thanks in advanced for any information.

[@NoOne-dv8wl]

I am not sure, but you might try IEX Cloud

[@SniperUSMC]

Would be nice to find gaps (rather than fill in data), like the one at time mark 7 min 58 seconds where it has a GAP down, 98% of the time GAPS fill within about two to three weeks, as both of the GAPS in that time frame of video have done. Find GAPS, look for first green bar and BUY. Trading GAPS can be very profitable. What do you think?

[@cfregis]

Data gap and price gap are two different animals

[@abcdefgh-kz8to]

Intro song ?

[@samtheharvester6188]

Please do this for crypto.

[@cryptolicious3738]

EPIC :D

[@TuranInsight]

Hi there! THank you so much for this incredible content! I wanna ask you if there is a small error here: ## select all closes for Friday vs. Previous Close SELECT stock_id, symbol, day, close, LAG (close,1) OVER (ORDER BY close ASC) AS previous_close FROM daily_bars JOIN stock ON stock.id = daily_bars.stock_id WHERE date(day) = '2021-02-05' ORDER BY stock_id, day; INstead of "(ORDER BY close asc) you should say( PARTITION BY stock_id ORDER BY day DESC) in order to get the previous close--- otherwise the query just puts up incorrect values( which I have confirmed myself dozens of times) which I dont know which date they are from? Let me know if i am missing something?