How to re-calcuate vwap from daily history bar

Alpaca Market Data
1

For example, this is a SPY daily history bar I retrieved using free API, is that possible to calculate the vwap column in another way that can be verified? I try to re-calculate vwap from cumulative 1 minute bars, but the result is different from this. What is the method to calculate daily vwap column?

open high low close volume trade_count vwap
timestamp
2023-01-03 05:00:00+00:00 384.370 386.430 377.8310 380.82 74330774 585048 380.961150
2023-01-04 05:00:00+00:00 383.180 385.880 380.0000 383.76 85425119 628498 383.151245
2023-01-05 05:00:00+00:00 381.720 381.760 378.7600 379.38 76079710 523811 380.264217
2023-01-06 05:00:00+00:00 382.610 389.250 379.4127 388.08 103741286 683816 385.248218
1 Like
2

@PC_Tang One thing to appreciate about the calculations for the Open, High, Low, Close, Volume, and VWAP values for a bar is they donā€™t typically 1) look at all the trades within the bar and 2) the different calculations look at different trades. There is more detail on this in this post.The price calculations in a bar exclude a number of trades, most notably any ā€˜odd lotā€™ trades less than 100. However, the VWAP calculation includes all trades (at least all non-info-only trades) and is sum(price x trade_volume) / sum(trade_volume) . So, there isnā€™t a way to calculate VWAP from minute bars. One would need to download all the trades and calculate it from those.

3

Thanks @Dan_Whitnable_Alpaca. I just want to estimate vwap unbiasly using my own way, but now it looks impossible because daily vwap is calculated by sum(price x trade_volume at that day) / sum(trade_volume at that day). I wonder if daily vwap is a live number or itā€™s only generated on a next day like minute bar, for example, if I query it at 3:59pm, it will aggregate all trades and volume from 4am until 3:59pm, can calculate vwap for me before market close which sounds a good estimation.

5

Thanks @Dan_Whitnable_Alpaca. I have figured out the way to calcuate daily vwap from minute bar. I wonder if Alpaca also provides the standard devation of volume weighted price at minute bar?

6

@PC_Tang Alpaca doesnā€™t currently provide anything but price, volume, and trade count. Standard Dev isnā€™t provided but is a good feature request. Thank you.

7

Hello PC_Tang,
Buddy Iam in the same boat. I need 1 minute vwap (Historical) for TQQQ and SPY. But Cannot find itā€¦
Do you find any luck getting the data??

Cheers!!
Popa

8

Hello Dan,

Is it possible we can pay to Alpaca and get historical 1 minute VWAP data for select instruments. In this case my research is specific to TQQQ and SPY
Thank you!!

9

@Popa_Canadian If you are looking for historical (ie more than 15 minutes old) 1 minute VWAP as calculated above

min_vwap = sum(price x trade_volume) / sum(trade_volume)

That is provided for free in the Basic Data plan. Simply fetch 1min bars using the historical bars endpoint. The VWAP is the vw field. The default is full market SIP data, but itā€™s good practice to explicitly state feed=sip to make sure. If you are looking for realtime data (ie the most recent 15 minutes) that is available in the Algo Trader Plus data plan for $99/mo.

Note this isnā€™t the VWAP that other platforms may provide (eg TradeStation). Those have a rolling VWAP averaged over a much larger time (eg 20 day). If that is what you are looking for, that can be calculated pretty easily using the python pandas rolling method using the data above.

10

Thanks Dan,
The price in your vwap formula? on a 1 minute bar we have open, high, low and close. which one I should use?

Thank you!!

11

@Popa_Canadian The VWAP is calculated from trade prices and not bar prices. Itā€™s the ā€˜volume weighted average priceā€™ of all the trades which occurred during the bar. Therefore the ā€œpriceā€ in the VWAP formula is the execution price of the trade.

Use the trades endpoint to fetch all the trades during a desired time. 2) filter to exclude any trades having trade conditions M, Q or 9 (which are ā€˜informational entries and not real tradesā€™) 3) for each trade multiply p x s (ie price x size) 4) sum those values for all trades 5) divide that sum by the sum of s (size or volume). That will be the VWAP during that desired time.

12

Thanks Dan,

I am new to Alpaca. Can you help me how to use the historical trades page.
I want TQQQ 1 minute data for last 10 years and then I can apply the formula as suggested by you.

I really appreciate your replies. Is there a way we can connect over a call?

Thank you!!

13

@Popa_Canadian I forgot to say before, but welcome to the Alpaca community. Iā€™ll try to help where I can.

You asked for help on ā€œhow to use the historical trades pageā€.
What you may really want is ā€œhistorical barsā€? The trades are every single executed trade and there are a lot (especially for a symbol like QQQ). There are on average more than 1000 trades/min. 10 years worth of trades is over 1,000,000,000 trades and a bit cumbersome to work with. Your original question was how to get 1 minute VWAP data. That is the vw field returned for every bar. Take a look at the docs here for info on the bars API. For example to get 1 minute bars for QQQ between 2024-08-05-2024-08-06 you would use the following URL

https://data.alpaca.markets/v2/stocks/bars?symbols=QQQ&timeframe=1Day&start=2024-08-05T00:00:00-04:00&end=2024-08-06T00:00:00-04:00

Which will return records for each bar similar to this (note the vw field is the VWAP for that bar)

  "bars": {
    "QQQ": [
      {
        "c": 399.32,
        "h": 399.32,
        "l": 399.04,
        "n": 30,
        "o": 399.04,
        "t": "2022-01-03T09:00:00Z",
        "v": 1428,
        "vw": 399.205357
      },

If you use python, there is the alpaca-py SDK or simply use the requests package to call the APIs directly.

Is there any other help you would like?

Also, Iā€™m not really available for a call, however you may want to join the Alpaca slack community (here is a link to join). I (and a lot of other members) are very active there and the format is much more interactive.

14

Hello Dan

First of all Thank you so much for such a detailed response. I really really appreciate.

I am a non technical person, does not programming languages so I am more like an excel guy can do complex modelling and formulas there. I was looking for a 1 min vwap data for tqqq in excel export/download that I can use further.

Sorry I am little weak in the programming concepts.

Once again. Thank you so much for taking out time and responding to my messages.

Cheers!!

15

Hello All,

I have an excellent VWAP strategy which is implement and it works easily for Alpaca.

https://www.quantbots.co/strategies_overview.html?portfolio=USETFFUND

At the moment I have covered some 3xETF and its giving me a great alpha. Let me know if anyone would like to test drive it and I am happy to help.

Thanks