Forum Discussion
Solved
Price tracking in power query
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 fr...
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.
Hi SYBTRON
Please try this
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"}}),
#"Added Custom3" = Table.AddColumn(#"Renamed Columns4", "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"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 , 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