exponential trend analysis for stock data in Power BI

[glichinsky61]

I am trying to do a freelance project for a financial advisor and I desperately need one-on-one help/advice. I have 4 months of experience working as a Power BI Developer doing API inventory/KPIs, after spending about 2 weeks of learning on Udemy, and I have some exposure to coding, so my knowledge is pretty minimal.
My client has an intricate excel workbook that is pulling daily stock data using excel's built-in stock market functionality. They are using exponential regression to find the slope and y intercept of the close price over date. I am trying to recreate his work in Power BI but I just cant get the right outcome. I really need someone with experience to help me out with this or to see if it is something that is even possible.

[AnkitaaMishra]

Hi @glichinsky61 , Thanks for reaching out!
To assist properly, we’ll need a small sample dataset and details on the Excel exponential regression formula. Community members can then help determine how to replicate the logic in Power BI.

[glichinsky61]

Alright, here is a sample data table:

DATE	OPEN	LOW	HIGH	CLOSE
1/29/1993	43.9688	43.75	43.9688	43.9375
2/1/1993	43.9688	43.9688	44.25	44.25
2/2/1993	44.2188	44.125	44.375	44.3438
2/3/1993	44.4063	44.375	44.8438	44.8125
2/4/1993	44.9688	44.875	45.0938	45
2/5/1993	44.9688	44.7188	45.0625	44.9688
2/8/1993	44.9688	44.9063	45.125	44.9688
2/9/1993	44.8125	44.5625	44.8125	44.6563
2/10/1993	44.6563	44.5313	44.75	44.7188

The formula to calculate the slope:
=INDEX(LOGEST($E$2:INDEX(E:E,COUNTA(E:E)),$A$2:INDEX(A:A,COUNTA(A:A)),),1)-1
Formula for the y intercept

=INDEX(LOGEST($E$2:INDEX(E:E,COUNTA(E:E)),$A$2:INDEX(A:A,COUNTA(A:A)),),2)
Column E is the CLOSE PRICE and A is the DATE

[v-menakakota · Accepted solution]

Hi @glichinsky61 ,

Thanks for reaching out to the Microsoft fabric community forum. 

I’ve tested this with your dataset and got the output as below in Power BI. The approach is to log-transform the Close price, perform a linear regression on DateIndex vs LogClose, and then convert the slope/intercept back to exponential form.

To help you get started, I’ve attached a PBIX file. It includes:

Your sample data
 Log transformation & date index
 Regression logic to calculate the exponential growth parameters
Predicted exponential trend line plotted against actual closing price.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.   

Best Regards, 
Community Support Team  

[v-menakakota]

Hi @glichinsky61 ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you 

Best Regards, 
Community Support Team  

[v-menakakota]

Hi @glichinsky61 ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you 

Best Regards, 
Community Support Team