Energy forcasting, factoring in Imported from Grid, Exported and Self consumed

[GLOpro]

Hi!

I am looking for help with a spreadsheet that contains the following daily CSV data


The sample below represents the top values in for the first 6 columns.

Utilising the daily data set (3yrs worth) how can i create a sheet that will find me the matching data set for a nominated period.

For example:
query 1: Find the best highest sum total for a (1,3,6,9,12month) rolling period for any of the first 6 columns.
query 2: Have a separate sheet that will allow me to add: 1. Solar Feed In Tariff 2. Daily Supply Charge and 3. Peak Usage Charge per kWh.

Thanks for everyones help and suggestions.

SolarEnergy Sample 2022.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Time Stamp	Energy Generated (Wh)	Energy Consumed (Wh)	Energy Expected (Wh)	PV Energy Consumed (Wh)	PV Energy Exported (Wh)	Grid Energy Imported (Wh)	PV Energy Consumed/ Energy Consumed	Generated/Imported	Exported - Imported mWh	Bar Graph
2	31/07/2022	23099.1191	25070.3875	23837.72016	7485.5498	15613.5896	17544.6183	30%	132%	-1.9310287	-1.9310287
3	30/07/2022	42977.7884	35656.8971	35832.69025	9903.8554	33073.9156	25714.0584	28%	167%	7.3598572	7.3598572
4	29/07/2022	42806.4486	17751.729	36713.20772	6995.3884	35811.0632	10809.882	39%	396%	25.0011812	25.0011812
5	28/07/2022	41509.7105	21596.6885	35067.24733	9438.4171	32071.2844	12166.154	44%	341%	19.9051304	19.9051304
6	27/07/2022	42822.2064	47095.9717	36478.83991	8142.668	34679.5425	38966.0862	17%	110%	-4.2865437	-4.2865437
7	26/07/2022	30664.2016	30306.9672	29454.20951	7051.8657	23612.367	23241.0467	23%	132%	0.3713203	0.3713203
8	25/07/2022	38825.8977	30489.334	35191.8201	9187.1216	29638.7711	21306.4603	30%	182%	8.3323108	8.3323108
9	24/07/2022	34369.8872	22442.4702	33912.66973	8844.0019	25525.8799	13567.6539	39%	253%	11.958226	11.958226
10	23/07/2022	16123.069	27476.6953	19938.37536	6980.4778	9142.6132	20544.0682	25%	78%	-11.401455	-11.401455
11	22/07/2022	17171.3368	34145.2888	20160.88154	4452.3542	12718.9898	29674.1236	13%	58%	-16.9551338	-16.9551338
12	21/07/2022	12668.6932	41594.1758	15077.08413	6152.8338	6515.8548	35435.1361	15%	36%	-28.9192813	-28.9192813
13	20/07/2022	14761.6283	41537.9379	14593.83532	6808.8615	7952.7417	34748.3724	16%	42%	-26.7956307	-26.7956307
14	19/07/2022	26193.0676	31477.3084	27049.30273	7562.0809	18630.9642	23897.6939	24%	110%	-5.2667297	-5.2667297
15	18/07/2022	39358.0295	27229.3515	34975.27298	11726.3834	27631.6481	15507.1404	43%	254%	12.1245077	12.1245077
16	17/07/2022	40393.0143	22960.6594	34803.26078	9811.6428	30581.3704	13144.4726	43%	307%	17.4368978	17.4368978
17	16/07/2022	23148.2353	45192.5389	27171.72787	7622.8728	15525.3882	37566.2588	17%	62%	-22.0408706	-22.0408706
18	15/07/2022	40071.4138	28312.1594	34056.77623	15876.653	24194.7364	12438.884	56%	322%	11.7558524	11.7558524
19	14/07/2022	28319.305	45127.9968	28219.12648	19640.5169	8678.7851	25509.7241	44%	111%	-16.830939	-16.830939
20	13/07/2022	32346.7548	24828.0022	30833.33749	6693.1916	25653.5706	18130.07	27%	178%	7.5235006	7.5235006
21	12/07/2022	29485.2095	35391.1888	31698.94105	13015.9343	16469.2994	22324.5256	37%	132%	-5.8552262	-5.8552262
22	11/07/2022	26790.8419	24284.9223	26037.06528	6109.0414	20681.823	18213.8601	25%	147%	2.4679629	2.4679629
23	10/07/2022	22888.3637	46373.6297	22163.21499	10844.743	12043.5696	35505.752	23%	64%	-23.4621824	-23.4621824
24	9/07/2022	35899.7423	21569.0648	33672.44728	7528.3066	28371.4284	14062.6152	35%	255%	14.3088132	14.3088132
25	8/07/2022	39847.7921	32806.5244	35191.8201	6456.7961	33391.0074	26346.7488	20%	151%	7.0442586	7.0442586
26	7/07/2022	17640.1366	22425.4377	24408.92505	8301.9543	9338.1884	14136.3594	37%	125%	-4.798171	-4.798171
27	6/07/2022	7091.907	32807.5081	12316.4905	5486.8872	1605.0232	27313.5192	17%	26%	-25.708496	-25.708496
28	5/07/2022	9620.0171	39584.7067	14449.17316	5923.0472	3697.1097	33655.4911	15%	29%	-29.9583814	-29.9583814
29	4/07/2022	4742.7413	23957.9756	8842.383572	3270.58	1472.0484	20686.9785	14%	23%	-19.2149301	-19.2149301
30	3/07/2022	4910.8243	35773.1274	8910.36658	4632.524	278.2767	31140.6962	13%	16%	-30.8624195	-30.8624195
31	2/07/2022	2861.6865	34911.1272	4557.219375	2719.0565	142.6332	32198.7789	8%	9%	-32.0561457	-32.0561457
32	1/07/2022	5468.1394	28338.4273	8776.856657	4571.0016	897.1115	23766.4193	16%	23%	-22.8693078	-22.8693078
33	30/06/2022	19924.7188	35926.3466	24092.84714	10386.4304	9538.3044	25529.6492	29%	78%	-15.9913448	-15.9913448
34	29/06/2022	28350.9667	37078.5159	29656.01336	12571.5342	15779.4135	24527.6832	34%	116%	-8.7482697	-8.7482697
35	28/06/2022	23438.3394	46032.501	25054.35072	10428.657	13009.6754	35584.5101	23%	66%	-22.5748347	-22.5748347
36	27/06/2022	28285.2867	35507.1224	27528.4744	8617.2896	19668.0061	26882.211	24%	105%	-7.2142049	-7.2142049
37	26/06/2022	22977.8712	21326.1118	26814.80167	10030.1563	12947.7324	11290.3966	47%	204%	1.6573358	1.6573358
38	25/06/2022	34183.8712	30346.3784	32130.02292	8601.9273	25581.9311	21762.4836	28%	157%	3.8194475	3.8194475
39	24/06/2022	19230.7639	26700.6656	19235.01109	9665.1836	9565.5837	17029.3099	36%	113%	-7.4637262	-7.4637262
40	23/06/2022	35402.5247	32691.2573	32672.12446	6802.2126	28600.3198	25910.5684	21%	137%	2.6897514	2.6897514
41	22/06/2022	36232.6229	34044.0256	31170.9634	18433.6131	17799.0257	15596.9807	54%	232%	2.202045	2.202045
42	21/06/2022	25194.9573	37692.2651	24131.21227	10204.3885	14990.5783	27483.4313	27%	92%	-12.492853	-12.492853
43	20/06/2022	21994.8637	33886.7349	20756.76072	8655.6181	13339.2525	25238.6458	26%	87%	-11.8993933	-11.8993933
44	19/06/2022	30448.9821	18097.5832	25041.38575	8978.2276	21470.7194	9133.8021	50%	333%	12.3369173	12.3369173
45	18/06/2022	33895.7503	18926.3539	30630.25016	7007.3794	26888.3785	11897.174	37%	285%	14.9912045	14.9912045
46	17/06/2022	32313.1396	32831.8011	29135.48767	7504.5552	24808.5752	25335.0998	23%	128%	-0.5265246	-0.5265246
47	16/06/2022	34101.2307	23218.8118	28915.27344	11184.6009	22916.6395	12035.8302	48%	283%	10.8808093	10.8808093
48	15/06/2022	38137.937	43299.5312	32305.87096	27209.6837	10928.2727	16087.2934	63%	237%	-5.1590207	-5.1590207
49	14/06/2022	38292.1103	33238.3094	32217.83014	18754.0083	19538.0951	14483.2295	56%	264%	5.0548656	5.0548656
50	13/06/2022	37252.7716	36349.7728	32251.16428	19806.908	17445.8389	16541.5667	54%	225%	0.9042722	0.9042722
51	12/06/2022	39084.3363	27183.2454	33758.30239	9126.3708	29957.9566	18032.7885	34%	217%	11.9251681	11.9251681
52	11/06/2022	39014.8824	36505.1956	33596.04688	10312.4681	28702.4209	26225.481	28%	149%	2.4769399	2.4769399
53	10/06/2022	39755.0434	46542.2845	33729.14897	21165.9124	18589.1313	25378.0969	45%	157%	-6.7889656	-6.7889656
54	9/06/2022	39622.6209	48992.9596	34234.912	18131.9233	21490.6971	30854.0883	37%	128%	-9.3633912	-9.3633912
55	8/06/2022	40675.8854	48636.4659	34098.27562	20803.2691	19872.6207	27840.4701	43%	146%	-7.9678494	-7.9678494
56	7/06/2022	37102.7953	35130.9935	32783.14459	11114.7739	25988.0167	24007.3394	32%	155%	1.9806773	1.9806773
57	6/06/2022	39913.209	39217.2931	34361.75441	9952.5703	29960.6379	29279.9464	25%	136%	0.6806915	0.6806915
58	5/06/2022	25094.8431	30158.3369	25412.94126	8125.9893	16968.861	22009.622	27%	114%	-5.040761	-5.040761
59	4/06/2022	39811.8713	18992.9693	33064.00837	9840.5269	29971.3698	9161.5953	52%	435%	20.8097745	20.8097745
60	3/06/2022	17560.3208	47632.2276	17057.31515	15223.1334	2337.1667	32421.0774	32%	54%	-30.0839107	-30.0839107
61	2/06/2022	30961.1515	52454.3408	27070.22662	21454.9763	9505.9608	30754.05	41%	101%	-21.2480892	-21.2480892
62	1/06/2022	41836.1472	59157.2357	34697.09625	30857.5257	10978.8258	28531.2585	52%	147%	-17.5524327	-17.5524327
63	31/05/2022	36562.0246	29383.4394	27983.84439	9351.6515	27210.3806	20040.9977	32%	182%	7.1693829	7.1693829
64	30/05/2022	11825.224	35272.3301	11644.03877	6636.3409	5188.8888	28649.8263	19%	41%	-23.4609375	-23.4609375
65	29/05/2022	41217.8773	29057.3404	33779.66774	8066.0145	33151.8683	20969.258	28%	197%	12.1826103	12.1826103
66	28/05/2022	28255.6739	17385.3945	27235.29303	7969.4262	20286.265	9403.0898	46%	300%	10.8831752	10.8831752
67	27/05/2022	37355.3797	17935.5272	31902.0901	9023.2614	28332.0486	8851.9583	50%	422%	19.4800903	19.4800903
68	26/05/2022	36840.3906	19191.5387	32009.72344	7102.1652	29738.2866	12140.3322	37%	303%	17.5979544	17.5979544
69	25/05/2022	32902.2854	21580.0815	30705.54488	8755.1774	24147.1103	12851.8882	41%	256%	11.2952221	11.2952221
70	24/05/2022	21672.6838	20663.7163	21379.42317	9304.7398	12367.9484	11354.5533	45%	191%	1.0133951	1.0133951
71	23/05/2022	28583.4535	24800.0384	23009.87903	9317.9436	19265.5107	15487.9858	38%	185%	3.7775249	3.7775249
72	22/05/2022	21144.457	18208.041	21174.73156	7325.3872	13819.0824	10860.9265	40%	195%	2.9581559	2.9581559
73	21/05/2022	14562.7576	19021.7409	13224.98312	7053.1398	7509.6243	11965.0978	37%	122%	-4.4554735	-4.4554735
74	20/05/2022	13554.7856	23269.9716	12050.9503	7330.745	6223.9994	15956.4357	32%	85%	-9.7324363	-9.7324363
75	19/05/2022	38144.3551	16517.3625	32769.37811	7355.3531	30788.9991	9173.0431	45%	416%	21.615956	21.615956
76	18/05/2022	41439.0475	24098.3578	34106.57733	10388.8659	31050.1873	13719.2677	43%	302%	17.3309196	17.3309196
77	17/05/2022	42675.6012	21586.9001	34245.25274	13062.0884	29613.495	8494.7036	61%	502%	21.1187914	21.1187914
78	16/05/2022	41848.3459	21245.7573	33588.83763	9328.0066	32520.3862	11926.8615	44%	351%	20.5935247	20.5935247
79	15/05/2022	13082.265	17729.1523	12934.46306	6224.0503	6858.2186	11474.0964	35%	114%	-4.6158778	-4.6158778
80	14/05/2022	23975.217	17341.6693	22731.91933	5697.5631	18277.6497	11651.1559	33%	206%	6.6264938	6.6264938
81	13/05/2022	14695.8139	20847.053	14757.92485	6995.2827	7700.5307	13869.3065	34%	106%	-6.1687758	-6.1687758
82	12/05/2022	6954.036	18212.1092	8290.159847	5457.6934	1496.3482	12761.0241	30%	54%	-11.2646759	-11.2646759
83	11/05/2022	7264.812	17196.6592	9484.48739	5693.4154	1571.3615	11521.5146	33%	63%	-9.9501531	-9.9501531
84	10/05/2022	33823.1526	19105.1699	28371.13699	9427.194	24395.9811	9667.9637	49%	350%	14.7280174	14.7280174
85	9/05/2022	15655.2064	18064.5566	14888.7	7283.0898	8372.0967	10784.3994	40%	145%	-2.4123027	-2.4123027
86	8/05/2022	44337.6429	15949.7869	32808.83948	7728.1812	36609.4566	8215.2089	48%	540%	28.3942477	28.3942477
87	7/05/2022	45180.4037	22089.0673	36204.93094	9897.7636	35282.645	12205.5944	45%	370%	23.0770506	23.0770506
88	6/05/2022	46507.4567	15862.7958	36023.53117	8120.7706	38386.701	7727.2223	51%	602%	30.6594787	30.6594787
89	5/05/2022	31400.8954	18662.6464	28317.86939	8424.8143	22976.0613	10176.5938	45%	309%	12.7994675	12.7994675
90	4/05/2022	39619.9812	21030.7657	32671.10029	9102.6821	30517.3395	11993.3707	43%	330%	18.5239688	18.5239688
91	3/05/2022	45429.9742	18662.662	34997.28964	9182.2216	36247.7412	9458.2633	49%	480%	26.7894779	26.7894779
92	2/05/2022	40315.7341	24451.302	32981.31101	10567.4506	29748.2759	13858.6114	43%	291%	15.8896645	15.8896645
93	1/05/2022	41706.4997	21661.956	34455.25397	10451.3262	31255.1528	11254.7413	48%	371%	20.0004115	20.0004115
177117-LO_-_Bardia-DailyDat (2)

Cell Formulas
Range	Formula
H2:H93	H2	=E2/C2
I2:I93	I2	=B2/G2
J2:J93	J2	=(F2-G2)/1000
K2:K93	K2	=J2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K2:K700	Other Type	DataBar		NO

 

[portews]

As for your Query #2, besides add in another sheet with those columns, I don't know what you want or how you get it based on what you've given us.

Here's what I did for Query #1:
Create a new sheet with the rolling month for each column. I assumed 30 days for each month and 365 for a year as shown (Because I don't have 6 months of data, the last 3 month ranges are all zero, you'll have to check those):

Time Stamp	Energy Generated (Wh)	Energy Consumed (Wh)	Energy Expected (Wh)	PV Energy Consumed (Wh)	PV Energy Exported (Wh)	Grid Energy Imported (Wh)
1 Month (30 days)	1018658.85	1067797.64	888351.6981	400951.81	649654.49	748670.78
3 Month (90 days)	2638696.84	2646285.47	2404431.465	889875.35	1748821.5	1759461.3
6 Month (180 days)	0	0	0	0	0	0
9 Month (270 days)	0	0	0	0	0	0
12 Month (365 days)	0	0	0	0	0	0

Then put in this code. it will loop through all the rows of data, one day at a time and check the monthly intervals and find the max. It then moves on to the next column and does the same. Then it moves on to the next set of monthly intervals and proceeds.

Sub findMaxes()
'https://www.mrexcel.com/board/threads/energy-forcasting-factoring-in-imported-from-grid-exported-and-self-consumed.1212802/
LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Dim MoInt As Variant
Dim CurrMax As Variant
Dim IsMax As Variant
Dim CurrRng As Range
MoInt = Array(30, 90, 180, 270, 365)
'for each Monthly Interval
For k = 0 To UBound(MoInt)
        'for each Energy Usage
        For j = 2 To 7
            'go through each row, one day at a time
            For i = 2 To LastRow - MoInt(k)
                'get the max sum for that monthly interval
                Set CurrRng = Range(Cells(i, j), Cells(i + MoInt(k) - 1, j))
                CurrMax = WorksheetFunction.Sum(CurrRng)
                'if it's bigger than the last one, save it
                If CurrMax > IsMax Then IsMax = CurrMax: Cells(k + 2, j) = IsMax
                CurrMax = 0
            Next i
        'print the max in the column at the top
        MyNewSheet.Cells(k + 2, j) = IsMax  'change MyNewSheet to your new sheetname
        IsMax = 0
        'go to the next energy usage
        Next j
'go to the next interval
Next k

End Sub