Hi Vidas, yes I did. I have tried with both configurations and both don't work. May be my configuration is "weird". I live in Spain but office is set up in english.
Reviewing my old file I also notice that column separator is no longer there in the new config file but I guess is normal:
Thanks, Antoni
PS: One more question. How is the folder structure supposed to be if I just work with V3? Should have a main folder called PortfolioSlicer and then inside those files & folders, right?
For DecimalSeparator value should be as in your first picture: , ColumnSeparator is no longer required in new v3 version as it is always Tab. I tested with European setup when decimal separator is , and thousand separator is . and it all worked well. Can you setup decimalSeaprator to proper value (,) and run UpdatePSData.bat. Then can you review each file in c:\PortfolioSlicer\PSData folder - do they looks OK? That is, do all files numbers in european format with , as decimal separator? If no, what files look not as expected?
In v3.0 there is just one requirement - all final csv extract file should be in c:\PortfolioSlicer\PSData folder, all other files - xlsx, .bat, quotes can be anywhere you want. The way you setup above is OK too.
@randywc Re: 1. Did I understood correctly - "savings" account is an account where there are just deposits, withdrawals and interest payments. There are no dividends, to symbols held, right? Correct, I track a savings account. V2 shows the interest in the dividend column and calculates a profit %. V3 shows the interest in the dividend column but does not calculate the profit %.
I setup account with 2 transactions: - TransType: Deposit 1000.00 cash - TransType: Int, price: 100.00 I can see calculated profit of 10% - as expected. Can you please give me any more info on when Profit% is not calculating for "savings" account. Do you add interest as transaction "Int" or do you use "Div" ?
2 quick things. #1 - responding to the original 32bit Office/64bit OS issue. I am only able to install the 32bit version of Microsoft Access Database Engine 2010 Redistributable that matches my Office installation. I am good with the workaround until I am able to install.
#2, I am a big fan of the old Last 12 day value changes by symbol. I tried to create the pivot table but the "fx Capital Gains" no longer exists. I tried "fx Unrlzd Cap Gain" however it returns totals rather than difference by day. Any chance you can point me in the right direction for adding this in?
I have attached a screen capture (V2 and V3) of where I do not see profit % calculated for an interest only account in V3. I add interest transaction using "Int". These are from the holdings tab.
@FoodIsMyPriority - regarding #2 - when you go to Daily worksheet, you can select symbol in the slicer (left) and yhou will see related daily value changes for that symbol. If you want to create separate pivot table with all the latest daily value changes, please use measure "Value Change".
Hi Vidas, I have been investigating a little bit more on the regional settings topic and I think the issue is coming from Power Bi, not from the scripts to generate quotes etc.
The script is generating the .csv with , as decimal separator as expected. But when reading the .csv file in powerbi / power query, the first 2 steps of powerquery are ok: you can see that it opens the .csv properly and headers are also promoted properly
But the last step "Changed type" to number, makes everything bad:
The solution I found is to add one additional step after promoting headers and tell PowerBi to change the regional settings for the affected column:
Select that is a number with spanish region:
ANd then your final step of changed type:
Obviously I can do the changes for my local version but may be you could add this step to the template to make sure it selects the local setting for all worlwide users.
If there is any better way, I would be glad to try it out.
Antoni - thank you very much for this feedback. It looks to me that I have to also release separate PowerBI file just for European users with comma as decimal separator. I will do that for next update.
Vidas, I have been a PS user for many years. Over the years I have resisted using the Quotes, CurrencyConv, Dividends, data files generated by PowerShell. I have duplicated these files for use in PS by using my own data harvesting methods with a daily updated database in SQL or MS Access. Past versions of PS have worked fine with my sourced files.
With the Beta of version 3 you have included data manipulation/adjustments in the PowerShell bridge between the source ".txt" files, and Excel source worksheets that is breaking my PS "process". The one that is causing me the most problem is the creation of the "CostBasisImpact" column that appears to be created in PowerShell as I do not see it in the Transactions sheet in "Source" worksheet but is in the "transactions.csv" file.
My request is to link the Source Excel file directly to the Reports Excel file without the PowerShell interface. To do this will require a new column in the transactions table named "CostBasisImpact". The other new PowerShell generated columns such as "refresh time" can also be handled in either the source file or PowerPivot.
All of the above comments relate to the second to last bullet point of the release notes: "External scripts now include additional PowerShell scripts that reads PortfolioSlicer-Source.xlsx workbook and extracts data into c:\PortfolioSlicer\PSData folder".
Daringler, CostBasisImpact calculation was moved partially into PowerShell because of technical limitations in PowerPivot (not supporting recursive calculations). Let's see how I can help resolve your issues. Are you creating your own Transactions.csv file? If so, why? I want to understand your process so that I might suggest some options.
Vidas, Thanks for your reply. Historically, PS initially had a separate Excel source file that was used to link to PowerPivot. No problem. Then in version 2, the source tables were included in the Excel reports file. No problem. In those two versions I believe PowerShell was only used to harvest quotes, dividends, etc.. I did not use PowerShell and developed my own process to harvest and clean that data.
In answer to your question, I have exported the Transactions table to a "csv" file using vba and also tried a direct link into the source table with PowerPivot similar to version 1. I also did this with all the source tables. There were some minor issues with some of the other tables that I was able to solve. The roadblock for me at the moment is the transactions table being modified between the source file and the reports file.
I have always calculated my cost basis and used that column in the transactions table. Could this be solved by continuing to use the "Cost Basis" column in some fashion so I would not have to insert PowerShell into my process?
In the v3.x version for Transactions.csv creation steps are: 1. Records from Transactions table are extracted into TransactionsInExcel.csv file. 2. PowerShell script GetExcelCostBasis.ps1 is executed that reads data from TransactionsInExcel.csv, does additional calculations and then creates file Transactions.csv.
As you are doing extracts yourself, the solution would be to Extract your Transactions into the file TransactionsInExcel.csv and then execute that PowerShell script using code:
cd c:\ProtfolioSlicer\YourRootFolderWereBatFilesAreLocated powershell -ExecutionPolicy Bypass .\Scripts\GetExcelCostBasis.ps1
Vidas, Sorry for the delay in response, I was out of town for a few days.
I did some more error tracking in the "CostBasisImpact" calculation I was using and found my problem. I am now able to extract all source tables to "csv files" and use them without using PowerShell. I get the same results as using the PowerShell process. I have vba extract the source tables to "csv files". I set up an active button that runs the vba, so anytime I update the Portfolio Slicer source file, I just press the button and it saves the file and exports the tables to csv files.
Comments
yes I did.
I have tried with both configurations and both don't work. May be my configuration is "weird". I live in Spain but office is set up in english.
Reviewing my old file I also notice that column separator is no longer there in the new config file but I guess is normal:
Thanks,
Antoni
PS: One more question. How is the folder structure supposed to be if I just work with V3? Should have a main folder called PortfolioSlicer and then inside those files & folders, right?
ColumnSeparator is no longer required in new v3 version as it is always Tab.
I tested with European setup when decimal separator is , and thousand separator is . and it all worked well.
Can you setup decimalSeaprator to proper value (,) and run UpdatePSData.bat. Then can you review each file in c:\PortfolioSlicer\PSData folder - do they looks OK? That is, do all files numbers in european format with , as decimal separator? If no, what files look not as expected?
In v3.0 there is just one requirement - all final csv extract file should be in c:\PortfolioSlicer\PSData folder, all other files - xlsx, .bat, quotes can be anywhere you want. The way you setup above is OK too.
Re:
1. Did I understood correctly - "savings" account is an account where there are just deposits, withdrawals and interest payments. There are no dividends, to symbols held, right? Correct, I track a savings account. V2 shows the interest in the dividend column and calculates a profit %. V3 shows the interest in the dividend column but does not calculate the profit %.
I setup account with 2 transactions:
- TransType: Deposit 1000.00 cash
- TransType: Int, price: 100.00
I can see calculated profit of 10% - as expected. Can you please give me any more info on when Profit% is not calculating for "savings" account. Do you add interest as transaction "Int" or do you use "Div" ?
2 quick things.
#1 - responding to the original 32bit Office/64bit OS issue. I am only able to install the 32bit version of Microsoft Access Database Engine 2010 Redistributable that matches my Office installation. I am good with the workaround until I am able to install.
#2, I am a big fan of the old Last 12 day value changes by symbol. I tried to create the pivot table but the "fx Capital Gains" no longer exists. I tried "fx Unrlzd Cap Gain" however it returns totals rather than difference by day. Any chance you can point me in the right direction for adding this in?
Thanks again for all your hard work,
I have attached a screen capture (V2 and V3) of where I do not see profit % calculated for an interest only account in V3. I add interest transaction using "Int". These are from the holdings tab.
Thanks
If you want to create separate pivot table with all the latest daily value changes, please use measure "Value Change".
I have been investigating a little bit more on the regional settings topic and I think the issue is coming from Power Bi, not from the scripts to generate quotes etc.
The script is generating the .csv with , as decimal separator as expected. But when reading the .csv file in powerbi / power query, the first 2 steps of powerquery are ok: you can see that it opens the .csv properly and headers are also promoted properly
But the last step "Changed type" to number, makes everything bad:
The solution I found is to add one additional step after promoting headers and tell PowerBi to change the regional settings for the affected column:
Select that is a number with spanish region:
ANd then your final step of changed type:
Obviously I can do the changes for my local version but may be you could add this step to the template to make sure it selects the local setting for all worlwide users.
If there is any better way, I would be glad to try it out.
Thanks,
Antoni
Thanks!
With the Beta of version 3 you have included data manipulation/adjustments in the PowerShell bridge between the source ".txt" files, and Excel source worksheets that is breaking my PS "process". The one that is causing me the most problem is the creation of the "CostBasisImpact" column that appears to be created in PowerShell as I do not see it in the Transactions sheet in "Source" worksheet but is in the "transactions.csv" file.
My request is to link the Source Excel file directly to the Reports Excel file without the PowerShell interface. To do this will require a new column in the transactions table named "CostBasisImpact". The other new PowerShell generated columns such as "refresh time" can also be handled in either the source file or PowerPivot.
All of the above comments relate to the second to last bullet point of the release notes: "External scripts now include additional PowerShell scripts that reads PortfolioSlicer-Source.xlsx workbook and extracts data into c:\PortfolioSlicer\PSData folder".
CostBasisImpact calculation was moved partially into PowerShell because of technical limitations in PowerPivot (not supporting recursive calculations).
Let's see how I can help resolve your issues.
Are you creating your own Transactions.csv file? If so, why? I want to understand your process so that I might suggest some options.
In answer to your question, I have exported the Transactions table to a "csv" file using vba and also tried a direct link into the source table with PowerPivot similar to version 1. I also did this with all the source tables. There were some minor issues with some of the other tables that I was able to solve. The roadblock for me at the moment is the transactions table being modified between the source file and the reports file.
I have always calculated my cost basis and used that column in the transactions table. Could this be solved by continuing to use the "Cost Basis" column in some fashion so I would not have to insert PowerShell into my process?
Thanks
Dennis
In the v3.x version for Transactions.csv creation steps are:
1. Records from Transactions table are extracted into TransactionsInExcel.csv file.
2. PowerShell script GetExcelCostBasis.ps1 is executed that reads data from TransactionsInExcel.csv, does additional calculations and then creates file Transactions.csv.
As you are doing extracts yourself, the solution would be to Extract your Transactions into the file TransactionsInExcel.csv and then execute that PowerShell script using code:
cd c:\ProtfolioSlicer\YourRootFolderWereBatFilesAreLocated
powershell -ExecutionPolicy Bypass .\Scripts\GetExcelCostBasis.ps1
Can you add such commands to your routine?
I did some more error tracking in the "CostBasisImpact" calculation I was using and found my problem. I am now able to extract all source tables to "csv files" and use them without using PowerShell. I get the same results as using the PowerShell process. I have vba extract the source tables to "csv files". I set up an active button that runs the vba, so anytime I update the Portfolio Slicer source file, I just press the button and it saves the file and exports the tables to csv files.
Thanks for your time.
Dennis
I have migrated from 2.4 to 3.0 and am not able to run the UpdatePSDataFromExcel.bat. I get the following error.
I have modified the psconfig file PSDATA section to where I really want it stored and not C:\
I have also downloaded the Access drivers (though I had MS Access already installed).
Any ideas? Thank you.