I am working with a sheet that handles commodities' future data (financial data, almost like stock data, but with monthly expiration dates that can go up to two to three years into the future).
My data has Symbols, expiration, and closing prices, which are about 600 rows. Each Commodity has a two-character code, for example;
COMMODITY CODE/ SYMBOL
'S&P 500 E-Mini' ES
'Nasdaq 100 E-Mini' NQ
'Russell 2000 E-Mini' RTY
'Dow Futures Mini' YM
'Gold' GC
'Corn' ZC
'Soybean' ZS
Each commodity has multiple monthly expiries going up to 3 or more years, but I want to work with only two years. Each month has a single-character code representing a month and the last two digits of the year. For example, January 2024 is encoded as "F24".
Month: Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Symbol: F G H J K M N Q U V X Z
The lazy way to prepare data table (FIXED SHEET):
FUTURE
Jan Feb Mar ..(months repeated for three years) Apr May Jun Jul Aug Sep Oct Nov Dec
2023 2024 (three years) 2025
F G H J K M N Q U V X Z (month codes)
F23 G23 H23 J23 K23 M23 N23 Q23 U23 V23 X23 Z23 (monthYEAR code as mYY)
ES 5618.5 5680.5
MES 5619.5 5673.5
YM 41480 41875
RTY 2213.9 2237
NQ 19445.75 19694
(Sorry, if it is too messy check the image of the sheet below!)
enter image description here
In the lazy method four years (48 columns of 'months') calendar, that represent the future expiry months, only dynamic value is year in the "anchored column C4". months columns have fixed month codes, starting from January code "F" and so on;
|← (Current year - 1) →|←(Current year)→|←(Current year +1) →|←(Current year+2)→|
The obvious problem is that when the current month is August "Q," the first 8 columns are empty. I want to show this calendar's range in another compact sheet, so empty values do no good to the sheet!
**The Dynamic data table (DYNAMIC SHEET): ** To show 24 months' worth data (~ two years) anchored columns B1 (for months) and B2 (for month and year code) get Current month in First column as MONTH(NOW()), and then to calculate next 24 months in C1 to Z1 I am using this formula;
=MONTH(EOMONTH(NOW(),0)+30+(IF(MOD(B1,2)=1,1,0)))
with subsequent column will 30,60,90,120..days to anchor column B1. If month has 31-day one extra day is added via formula IF(MOD(B1,2)=1,1,0)
To encode last 2 digits of year and the month code, I am using this formula in anchor column B2;
=CHOOSE(B1,"F","G","H","J","K","M","N","Q","U","V","X","Z") & RIGHT(YEAR(NOW()),2) then subsequent expiry columns C2,D2...Z2 are calculated as below:
=CHOOSE(C1,"F","G","H","J","K","M","N","Q","U","V","X","Z")&RIGHT(YEAR(EOMONTH(NOW(),0)+30+(IF(MOD(A1,2)=1,1,0))),2)
To auto calculate year, I am adding extra days 30,30..720.
To play with sheet, make a copy:
https://docs.google.com/spreadsheets/d/1ThGY8cH0zRTQSWM2k7aHCwZrnVpd88gXXS7jgx9Iy2s/edit?usp=sharing
So far, my app is working, I am using it to trade the futures contract, I was wondering if I can improve the performance or efficiency.
Rows DYNAMIC!1:2 are repeating now() a lot. That function refreshes every time you edit the spreadsheet. In the CONFIG sheet, put =today() in a cell, and replace all instances of now() with a reference to that cell.
The formulas in DYNAMIC!1:2 each filter the data separately, which is computationally more intensive than just doing the lookup. You can remove filter() like this:
=arrayformula(ifna(vlookup(
A3:A50 & B2:Z2, { CONFIG!G2:G & CONFIG!H2:H, CONFIG!I2:I }, 2, false
)))
Clear row DYNAMIC!B3:3 and put the formula in cell B3. It will give the same results as the multiple formulas you currently have in that row.
To improve spreadsheet performance further, see my optimization tips.