Guild of Project Controls: Compendium | Roles | Assessment | Certifications | Membership

Tips on using this forum..

(1) Explain your problem, don't simply post "This isn't working". What were you doing when you faced the problem? What have you tried to resolve - did you look for a solution using "Search" ? Has it happened just once or several times?

(2) It's also good to get feedback when a solution is found, return to the original post to explain how it was resolved so that more people can also use the results.

Spider Project Automation

8 replies [Last post]
Bogdan Leonte
User offline. Last seen 2 weeks 6 days ago. Offline
Joined: 18 Aug 2012
Posts: 240

Vladimir,

I am looking for a way to do update all material and resources costs on a weekly basis.

Here is what I am thinking:
01. Use a database (doesn't matter wich one) as long as it can export a simple text file. The database will store codes of materials/resources, description, unit of measure, cost/volume or cost/hour (in case of resources), date of costs. The costs can be taken from accounting of from quotations.
02. On the last day of the week/first day of the week the database will export the latest costs for all materials and resources in a text file to a certain location.
03. I want to have a macro (internal/external) which will be triggered when opening Spider Project (maybe run in the background) that will create a reference book for materials from that text file and overwrite the existing one. (I do not need to create a new version because I have the database that can give me history of costs).
04. The macro from 03. will open a certain project portfolio/s and transfer the reference book of materials to the portfolio, perform cost calculation, save project portfolio and distribute projects.
05. In the end the macro will end by closing Spider Project.

Is this possible?

Best regards,
Bogdan

Replies

Bogdan,

One option is to create separate reference-books for each region and link projects with corresponding reference-books.

Another option that you mentioned is to create different material codes based on the region or the supplier and create material centers for the same materials supplied by different vendors for reporting overall consumption and supply.

First option is preferable if to work with the project fragments. It is sufficient to apply different reference-books for adjusting the fragment to corresponding regions. No need to modify codes. But there will be problems if projects from different regions are included in the same portfolio and portfolio cost must be recalculated.

Bogdan Leonte
User offline. Last seen 2 weeks 6 days ago. Offline
Joined: 18 Aug 2012
Posts: 240

Vladimir,

I have encoutered the following situation, which requires your experience and expertise.

Projects that develop in different regions may have the same materials but different vendeos and different prices. The same goes for concrete and asphalt, which is tied to the region (place) of the project.

How do you recommend building a material, material sets reference-books?

I can only think of coding the vendor into the code of the material and create different material sets for each vendor. This would be easier to work than to replace for each set the material according to vendor. Of course this is not fullproof because sometimes you buy each material from a different vendor in order to minimize costs.

Even though this is not related to the topic, how would you reccomend building project fragnents? Would you create a fragnent that includes several materials and material sets from different vendors or build a fragnent for each vendor?

Best regards,
Bogdan

Bogdan Leonte
User offline. Last seen 2 weeks 6 days ago. Offline
Joined: 18 Aug 2012
Posts: 240

Vladimir,

I will take you option into consideration and come back to it when I have a better picture if it is feasible for what I need/want.

Best regards,
Bogdan

I agree that for detailed modeling it is better to use cost periods.

But if there are few groups of materials with similar inflation rates it is possible to create and to use corresponding cost components with different discounting rates (like all metal, all concrete, etc.).

Not so precise but easier to make changes, simulate risks, etc.

Bogdan Leonte
User offline. Last seen 2 weeks 6 days ago. Offline
Joined: 18 Aug 2012
Posts: 240

Vladimir,

Discount rate for cost components is the right choise for inflation or for a somewhat uniform growth of certain cost components. Howerver in the current "climate" it is not enough because not all materials grow by the same rate.

A few examples: concrete 50% from 2021, cement 50% from 2021, steel: 100% from 2020, partition walls (steel structure and boards) 12%, crushed stone 2.7% and so on. Cost periods for materials would be better.

Of course we shall not take and insert cosf growth for screws of the partition walls but the same growth for all materials that go into the partition walls would be enough...at most metal parts will have one coefficient and the rest of the materials another one.

This is why I belive that either generating cost periods from a material data base, or probable and maximum growth rate for materials (types/sets) would be a better option.

Best regards,
Bogdan

Bogdan,

Macro and Script are the same. You can run scripts not only within Spider but from any external software or from the command line adding open Spider command.

I suggest to try another approach for modelling price increase.

Spider Project simulates discounting and for each cost compnent you can set its own discounting rate. But if you will set this rate as negative Spider Project will simulate inflation or price increase. This is easier than setting price increase for each material or resource using cost periods.

Let me know if this approach works for you.

Bogdan Leonte
User offline. Last seen 2 weeks 6 days ago. Offline
Joined: 18 Aug 2012
Posts: 240

Thank you Vladimir for the reply.

Saving the material reference-book into spider after it is created from the CSV file would be desired and the version increased with each new reference-book created.

I am still struggling to determine the best structure for such a data base since the more I work on it I discover there are many pieces of information I want to transfer into Spider.

Issue #1:

Another piece of information I would like to transfer is cost periods for materials. Imagine you have material Y that was bought in year 1, months 2, 4, 7, 8 and in year 2 months 1, 2, 3, 4, 5, 6, 7. You will have an increase in price that can be determined and thus with some degree of error anticipated.

I would like to be able to transfer a number of cost periods for this materiale...say an increase of 1.1% every month from the start of the project to the end. Would that also be possible? or would it be better to calculate the estimated price of the material as a different cost component and transfer it into Spider?

As I wrote this I though it might be better to transfer a maximum price increase and a reasonable price increase into risk analysis fileds such as monte carlo or create additional reference books for 3 scenarios. What do you think?

Issue #2:

One other question what is the difference between a macro and a general script that can be run from the main Spider window. I know I have asked this question before but it still isn't clear.

Can I have scripts saved as files, imported in the Spider library and then call them using Macro?
Can I have scripts saved in the script library and then call them using Macro?

Best regards,
Bogdan

Hi Bogdan,

sorry, I missed your post.

If you will create text (CSV) files with column codes that are the same as in corresponding reference-books, required script (Macro) may look like this:

OpenProj (Portfolio1, 1, My Portfolio);//

CreateDocText (C:\Users\komar\Documents\Spider Project\Documents\Matreials.txt);//

DocSetConnectingFields (Code);//

DocSetProp ( TYPE : MAT);//

SetDocApply (TYPE : NUM, ADD : Y, EDIT : Y, FILTER : N, TOFILTER : N);//

DocApplyCurrProj ();//

DocClose ();//

CreateDocText (C:\Users\komar\Documents\Spider Project\Documents\Resources.txt);//

DocSetConnectingFields (Code);//

DocSetProp (TYPE : RES);//

DocApplyCurrProj ();//

DocClose ();//

ProjVerInc ();//

ProjCalcCost ();//

ProjPortfolioDistributeCurr ();//

ProjSave ();//

ProjClose ();//

 

I understood that saving reference-books is not needed. They are kept as text files.

This script increases portfolio version number when applied.