want to track the price changes, price rising trends after expiry of current month till 15 days left up to the next expiry, how much percentage stock has rose in positive. and percentage variation from -5% to 0 and 0 to 5% till 15 days are left for expiry.
Example: Here 29/12/2022 was expiry and data stared from next day i.e 30-12-2022, 15 day before next month expiry is 11-01-2023. Rising percentage till 11-01-2023 (separate column for simplicity) , then percentage variation from 12-01-2023 to 26-01-2023(expiry) only data should be -5%to0 (separate column for simplicity)or 0to5% (separate column for simplicity).
Can you modify the code little
1) In custom.2 i want only the percentage rise before 15th day of month expiry(11-01-2023) (The data written in custom.1).
2) A new column as custom.3 at the day of expiry if the pecentage of stock rise or fall
i.e (-5% to 0) or ( 0 to 5%) compare to price of stock on 15th day of current month expiry(11-01-2023).
IF any of the day is holiday i.e data is not there it can consider earlier date.
3) In power query, Advance editor , already some data is there in my file, so how to use your code.
4) I do not want that red marked data only the yellow marked field
Hi @SYBTRON
Put the following code to advanced editor in power query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdJLCgMxCADQu2Q9GD/56FmGWfT+l2hKKSgodBUSHv7ifbdXu5pgJ+6MzOdiBDjPyavT503ac30Z/x4OYrDUiDMCIzXDGYKRJ5sOIWiOVkBF2RbQSg1hqIlzRGEARTpyY1IrRkAS28vRCpGsyLe9yon+FciCmsUSYIyVI9eeavV7PKLKkVsEnUBFKDeErbAjet4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Stock = _t, Date = _t, #"Closing Price" = _t, Expiry = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Stock", type text}, {"Date", type text}, {"Closing Price", type number}, {"Expiry", type text}}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}, {"Expiry", type date}}, "en-GB"),
#"Added Custom3" = Table.AddColumn(#"Changed Type with Locale", "Weekday", each Date.DayOfWeek([Date])),
#"Added Custom" = Table.AddColumn(#"Added Custom3", "Custom", each let a=Date.AddDays([Expiry],-28),
b=Date.AddDays([Expiry],-15)
in if [Date]>a and [Date]<=b then "from "&Text.From(a)&" to"&Text.From(b) else if [Date]>b and [Date]<=[Expiry] then "from "&Text.From(b)&" to "&Text.From([Expiry]) else null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each let _Mindate=List.Min(Table.SelectRows(#"Added Custom",(x)=>x[Stock]=[Stock] and x[Custom]=[Custom])[Date]),
_minprice=List.Min(Table.SelectRows(#"Added Custom",(x)=>x[Stock]=[Stock] and x[Custom]=[Custom] and x[Date]=_Mindate)[Closing Price])
in
([Closing Price]-_minprice)/_minprice),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each if [Custom.1]>=-0.5 and [Custom.1]<0 then
"-5% to 0" else if [Custom.1]>=0 and [Custom.1]<0.5 then " 0 to 5%" else "Rising"),
#"Added Custom4" = Table.AddColumn(#"Added Custom2", "Custom.3", each let a=Date.AddDays([Expiry],-15),
b=List.Max(Table.SelectRows(#"Added Custom2",(x)=>x[Stock]=[Stock] and x[Date]<[Date] and x[Weekday]<>6 and x[Weekday]<>0)[Date]),
c=List.Max(Table.SelectRows(#"Added Custom2",(x)=>x[Stock]=[Stock] and x[Date]=b)[Custom.1])
in if [Date]=a and [Weekday]<>6 and [Weekday]<>0 then [Custom.1] else if [Date]=a and ([Weekday]=6 or [Weekday]=0) then c else null),
#"Added Custom6" = Table.AddColumn(#"Added Custom4", "Custom.4", each let a=Date.AddDays([Expiry],-15),
b=List.Max(Table.SelectRows(#"Added Custom2",(x)=>x[Stock]=[Stock] and x[Date]<[Date] and x[Weekday]<>6 and x[Weekday]<>0)[Date]),
c=List.Max(Table.SelectRows(#"Added Custom2",(x)=>x[Stock]=[Stock] and x[Date]=b)[Custom.2])
in if [Date]=a and [Weekday]<>6 and [Weekday]<>0 then [Custom.2] else if [Date]=a and ([Weekday]=6 or [Weekday]=0) then c else null),
#"Added Custom5" = Table.AddColumn(#"Added Custom6", "Custom.5", each let a=Date.AddDays([Expiry],-1),
b=List.Max(Table.SelectRows(#"Added Custom2",(x)=>x[Stock]=[Stock] and x[Date]<[Date] and x[Weekday]<>6 and x[Weekday]<>0)[Date]),
c=List.Max(Table.SelectRows(#"Added Custom2",(x)=>x[Stock]=[Stock] and x[Date]=b)[Custom.2])
in if [Date]=a and [Weekday]<>6 and [Weekday]<>0 then [Custom.2] else if [Date]=a and ([Weekday]=6 or [Weekday]=0) then c else null),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom5",{"Weekday", "Custom", "Custom.1", "Custom.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Custom.3", Percentage.Type}})
in
#"Changed Type1"Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Not working getting error while doing it.
Below is my code
let
Source = Folder.Files("D:\STOCK ANALYSIS\stock analysis 3\equity"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type text}, {"Column2", Int64.Type}, {"Column3", type number}, {"Column4", type number}, {"Column5", type number}, {"Column6", type number}, {"Column7", Int64.Type}, {"Column8", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name", "Column8", "Column3", "Column4", "Column5"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1", "Stock"}, {"Column2", "Date"}, {"Column6", "Closing price"}, {"Column7", "Volume"}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Volume"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns1", "Custom", each fn([Date], -1, Day.Thursday)),
#"Removed Columns2" = Table.RemoveColumns(#"Added Custom",{"Custom"}),
#"Inserted Date" = Table.AddColumn(#"Removed Columns2", "Date.1", each Date.From(Text.From([Date], "en-IN")), type date),
#"Removed Columns3" = Table.RemoveColumns(#"Inserted Date",{"Date"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns3",{{"Date.1", "Date"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns2",{"Stock", "Date", "Closing price"}),
#"Added Custom1" = Table.AddColumn(#"Reordered Columns", "Custom", each fn([Date], -1, Day.Thursday)),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", type date}}),
#"Renamed Columns3" = Table.RenameColumns(#"Changed Type1",{{"Custom", "Expiry"}}),
#"Sorted Rows" = Table.Sort(#"Renamed Columns3",{{"Stock", Order.Ascending}, {"Date", Order.Ascending}}),
#"Renamed Columns4" = Table.RenameColumns(#"Sorted Rows",{{"Closing price", "Closing Price"}})
in
#"Renamed Columns4"
not working , can you directly make change to my excelhttps://docs.google.com/spreadsheets/d/1r9aNz_dNGMjtkSu3BVyZC1K_93kB5FOD/edit?usp=drive_link&ouid=108855775676554667880&rtpof=true&sd=true
Hi @SYBTRON
You can put the following code to advanced editor in power query.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdJLCgMxCADQu2Q9GD/56FmGWfT+l2hKKSgodBUSHv7ifbdXu5pgJ+6MzOdiBDjPyavT503ac30Z/x4OYrDUiDMCIzXDGYKRJ5sOIWiOVkBF2RbQSg1hqIlzRGEARTpyY1IrRkAS28vRCpGsyLe9yon+FciCmsUSYIyVI9eeavV7PKLKkVsEnUBFKDeErbAjet4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Stock = _t, Date = _t, #"Closing Price" = _t, Expiry = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Stock", type text}, {"Date", type text}, {"Closing Price", type number}, {"Expiry", type text}}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}, {"Expiry", type date}}, "en-GB"),
#"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Custom", each let a=Date.AddDays([Expiry],-28),
b=Date.AddDays([Expiry],-15)
in if [Date]>a and [Date]<=b then "from "&Text.From(a)&" to"&Text.From(b) else if [Date]>b and [Date]<=[Expiry] then "from "&Text.From(b)&" to "&Text.From([Expiry]) else null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each let _Mindate=List.Min(Table.SelectRows(#"Added Custom",(x)=>x[Stock]=[Stock] and x[Custom]=[Custom])[Date]),
_minprice=List.Min(Table.SelectRows(#"Added Custom",(x)=>x[Stock]=[Stock] and x[Custom]=[Custom] and x[Date]=_Mindate)[Closing Price])
in
([Closing Price]-_minprice)/_minprice),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each if [Custom.1]>=-0.5 and [Custom.1]<0 then
"-5% to 0" else if [Custom.1]>=0 and [Custom.1]<0.5 then " 0 to 5%" else "Rising"),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2",{{"Custom.1", Percentage.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Custom"})
in
#"Removed Columns"Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Can you modify the code little
1) In custom.2 i want only the percentage rise before 15th day of month expiry(11-01-2023) (The data written in custom.1).
2) A new column as custom.3 at the day of expiry if the pecentage of stock rise or fall
i.e (-5% to 0) or ( 0 to 5%) compare to price of stock on 15th day of current month expiry(11-01-2023).
IF any of the day is holiday i.e data is not there it can consider earlier date.
3) In power query, Advance editor , already some data is there in my file, so how to use your code.
4) I do not want that red marked data only the yellow marked field
Hi @SYBTRON
Put the following code to advanced editor in power query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdJLCgMxCADQu2Q9GD/56FmGWfT+l2hKKSgodBUSHv7ifbdXu5pgJ+6MzOdiBDjPyavT503ac30Z/x4OYrDUiDMCIzXDGYKRJ5sOIWiOVkBF2RbQSg1hqIlzRGEARTpyY1IrRkAS28vRCpGsyLe9yon+FciCmsUSYIyVI9eeavV7PKLKkVsEnUBFKDeErbAjet4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Stock = _t, Date = _t, #"Closing Price" = _t, Expiry = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Stock", type text}, {"Date", type text}, {"Closing Price", type number}, {"Expiry", type text}}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}, {"Expiry", type date}}, "en-GB"),
#"Added Custom3" = Table.AddColumn(#"Changed Type with Locale", "Weekday", each Date.DayOfWeek([Date])),
#"Added Custom" = Table.AddColumn(#"Added Custom3", "Custom", each let a=Date.AddDays([Expiry],-28),
b=Date.AddDays([Expiry],-15)
in if [Date]>a and [Date]<=b then "from "&Text.From(a)&" to"&Text.From(b) else if [Date]>b and [Date]<=[Expiry] then "from "&Text.From(b)&" to "&Text.From([Expiry]) else null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each let _Mindate=List.Min(Table.SelectRows(#"Added Custom",(x)=>x[Stock]=[Stock] and x[Custom]=[Custom])[Date]),
_minprice=List.Min(Table.SelectRows(#"Added Custom",(x)=>x[Stock]=[Stock] and x[Custom]=[Custom] and x[Date]=_Mindate)[Closing Price])
in
([Closing Price]-_minprice)/_minprice),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each if [Custom.1]>=-0.5 and [Custom.1]<0 then
"-5% to 0" else if [Custom.1]>=0 and [Custom.1]<0.5 then " 0 to 5%" else "Rising"),
#"Added Custom4" = Table.AddColumn(#"Added Custom2", "Custom.3", each let a=Date.AddDays([Expiry],-15),
b=List.Max(Table.SelectRows(#"Added Custom2",(x)=>x[Stock]=[Stock] and x[Date]<[Date] and x[Weekday]<>6 and x[Weekday]<>0)[Date]),
c=List.Max(Table.SelectRows(#"Added Custom2",(x)=>x[Stock]=[Stock] and x[Date]=b)[Custom.1])
in if [Date]=a and [Weekday]<>6 and [Weekday]<>0 then [Custom.1] else if [Date]=a and ([Weekday]=6 or [Weekday]=0) then c else null),
#"Added Custom6" = Table.AddColumn(#"Added Custom4", "Custom.4", each let a=Date.AddDays([Expiry],-15),
b=List.Max(Table.SelectRows(#"Added Custom2",(x)=>x[Stock]=[Stock] and x[Date]<[Date] and x[Weekday]<>6 and x[Weekday]<>0)[Date]),
c=List.Max(Table.SelectRows(#"Added Custom2",(x)=>x[Stock]=[Stock] and x[Date]=b)[Custom.2])
in if [Date]=a and [Weekday]<>6 and [Weekday]<>0 then [Custom.2] else if [Date]=a and ([Weekday]=6 or [Weekday]=0) then c else null),
#"Added Custom5" = Table.AddColumn(#"Added Custom6", "Custom.5", each let a=Date.AddDays([Expiry],-1),
b=List.Max(Table.SelectRows(#"Added Custom2",(x)=>x[Stock]=[Stock] and x[Date]<[Date] and x[Weekday]<>6 and x[Weekday]<>0)[Date]),
c=List.Max(Table.SelectRows(#"Added Custom2",(x)=>x[Stock]=[Stock] and x[Date]=b)[Custom.2])
in if [Date]=a and [Weekday]<>6 and [Weekday]<>0 then [Custom.2] else if [Date]=a and ([Weekday]=6 or [Weekday]=0) then c else null),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom5",{"Weekday", "Custom", "Custom.1", "Custom.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Custom.3", Percentage.Type}})
in
#"Changed Type1"Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
